Oracle: Use NVL() to convert a null value into another value.

When you retrieve data from oracle database,

when some records has null value in some columns,

if you want to display the records more meaningful,

with the columns whose value is null being displayed with a  meaningful string,

you can turn help to one of the oracle's built-in functions: NVL().

NVL() allows you to convert a null value into another value.

NVL() accepts two parameters: a column, and the value that should be substituted.

In the following example, NVL() is used to convert a null value in the "name " column to the string "The name for this fruit is unknown".

Data preparation:

create table fruit
(
  id        NUMBER(4)       primary key,
  name      VARCHAR2(150)   
);

insert into fruit values(1, 'Apple' );
insert into fruit values(2, 'Banana');
insert into fruit values(3, 'Pear' );
insert into fruit values(4, null);

commit;

select * from fruit;  And the output is:

id                     name

1                       Apple

2                       Banana

3                       Pear

4                       null

select id,  NVL(name,'The name for this fruit is unknown') name from fruit; And the output is:

id                    name

1                     Apple

2                     Banana

3                     Pear

4                     The name for this fruit is unknown