Let Us Learn Oracle - Part 12 of N [ NVL function in Oracle ]

Source table: tblPlayers

Purpose: It substitute a value when a null value is encountered.

Syntax: NVL( ColumnName, ReplaceValue )

Where,

ColumnName => It is the column that is use for null value test.

ReplaceValue => Value that will be substituted if column value is null

For the sake of illustration we have inserted one record where the Fee PerMatch is null.

Insert into tblplayers(PLAYERID,PLAYERFIRSTNAME,PLAYERLASTNAME,BELONGSTO,DOB,FEEPERMATCH)  Values(11,'P','Chopra','India',To_Date('14/02/1998','DD/MM/YYYY'),Null);

Now run the query

SQL> Select  PLAYERID,Concat(PLAYERFIRSTNAME,Concat(' ' , PLAYERLASTNAME)) PlayerName,NVL(FEEPERMATCH,55) From tblplayers Order By PLAYERID;

  PLAYERID PLAYERNAME            NVL(FEEPERMATCH,55)
---------- --------------------- -------------------
         1 A Raman                              2000
         2 B Kadir                              1000
         3 C Nadir                              3000
         4 D Das                                 500
         5 E Punchkar                          12000
         6 F Fateman                           22000
         7 G Gajani                              999
         8 H Hariharan                          7000
         9 I Ikat                               5999
        10 J Jwar                               7900
        11 P Chopra                               55

11 rows selected.

We can make out that for PlayerId 11, the value for FeePerMatch has been replaced by 55.

In Sql Server, we implement this functionality by using either Coalesce, or Null IF, Or Case statement, Or IIF (in Denali).

Thanks for reading.