Posted By

bbrumm on 12/30/16


Tagged

sql Oracle


Versions (?)

Oracle NVL Function


 / Published in: SQL
 

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

A few examples of the Oracle NVL function with some sample data.

  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;

Report this snippet  

You need to login to post a comment.