Advanced Search

Oracle NVL Function

/ Published in: SQL
Save to your folder(s)
A few examples of the Oracle NVL function with some sample data.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DROP TABLE customers;
  2.  
  3. CREATE TABLE CUSTOMERS (
  4. first_name varchar2(100),
  5. last_name varchar2(100),
  6. country varchar2(20),
  7. employees NUMBER,
  8. start_date DATE
  9. );
  10.  
  11. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('John', 'Smith', 'USA', 4, '12-APR-2010');
  12. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Sally', 'Jones', 'USA', 10, '04-JUL-2011');
  13. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Steve', 'Brown', 'Canada', 15, '21-MAR-2009');
  14. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Mark', 'Allan', 'UK', 23, '1-FEB-2001');
  15. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Adam', 'Cooper', 'USA', 55, NULL);
  16. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Josh', 'Thompson', NULL, 1, '10-FEB-2012');
  17. INSERT INTO CUSTOMERS (first_name, last_name, country, employees, start_date) VALUES ('Peter', 'Manson', 'France', NULL, '16-OCT-2012');
  18.  
  19.  
  20.  
  21. SELECT * FROM CUSTOMERS;
  22.  
  23.  
  24. /* Examples */
  25.  
  26. /* 1 - string */
  27.  
  28. SELECT first_name, last_name, country, NVL(country, 'No country')
  29. FROM customers;
  30.  
  31. /* 2 - numbers */
  32.  
  33. SELECT first_name, last_name, employees, NVL(employees, 0) "Fixed Employees"
  34. FROM customers;
  35.  
  36. /* 3 - dates */
  37.  
  38. SELECT first_name, last_name,  start_date, NVL(start_date, TO_DATE('01-JAN-2000')) "Fixed Start Date"
  39. FROM customers;
  40.  
  41. /* 4 - another column as return */
  42.  
  43. SELECT first_name, last_name, country, NVL(country, last_name)
  44. FROM customers;
  45.  
  46. /* 5 - multiple columns */
  47.  
  48. SELECT first_name, last_name,  
  49. NVL(country, 'No country') "Country", 
  50. NVL(employees, 0) "Employees",
  51. NVL(start_date, TO_DATE('01-JAN-2000')) "Start Date"
  52. FROM customers;

URL: http://www.databasestar.com/oracle-nvl/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.