Oracle SQL - Function NVL function

Introduction

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;

Demo

SQL>
SQL>-- from   w  w w.j av  a2  s  . c  o 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>
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)
  2
SQL> SELECT lineno, NVL(value, 10)
  2  From my_table;

   LINENO | NVL(VALUE,10)
--------- | -------------
 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.

SQL>

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.

Oracle SQL - Function NVL function

Introduction

Demo

Related Topics