Oracle SQL - Function NVL function


NVL function is used if there are null values.

NVL Returns a substitute if a value is null.

NVL takes two arguments.

  • The first argument is the field or attribute which could be null, and
  • The second argument is the value that you want to replace the null value by.

"NVL(value, 10)" means that we would like to replace the null value in the "value" column by 10.

SELECT lineno, NVL(value, 10) 
From my_table;


SQL>-- w ww.  j  av  a 2s  .  co  m
SQL> drop table my_table;

Table dropped.

SQL> CREATE TABLE my_table (
  2      LINENO NUMBER(2) not null,
  3      VALUE NUMBER(6,2) not null
  4  );
SQL> insert into my_table values(1,1.2);
SQL> insert into my_table values(2,123.34);
SQL> insert into my_table values(3,-12.2);
SQL> insert into my_table values(4,100);
SQL> insert into my_table values(5,48);
SQL> insert into my_table values(6,-90);
SQL> insert into my_table values(7,0.19);
SQL> INSERT INTO my_table values(8, NULL)
SQL> SELECT lineno, NVL(value, 10)
  2  From my_table;

--------- | -------------
 00001.00 |      00001.20
 00002.00 |      00123.34
 00003.00 |     -00012.20
 00004.00 |      00100.00
 00005.00 |      00048.00
 00006.00 |     -00090.00
 00007.00 |      00000.19

7 rows selected.


NVL does not change the actual data in the table.

It allows you to use some number in place of null in the SELECT statement.

Related Topics