NVL

Compatibility

NVL(f1, f2)
Engine	OK	Alternative
ingres	No	COALESCE(f1, f2)
mysql	No	COALESCE(f1, f2)
oracle	Yes	COALESCE(f1, f2)
postgres	No	COALESCE(f1, f2)
sqlserver	No	COALESCE(f1, f2)

NVL

NVL takes two arguments and returns the first value that is not null.

   NVL(x,y) = x if x is not NULL
   NVL(x,y) = y if x is NULL

NVL can be useful when you want to replace a NULL value with some other value. In this example you show the name of the party for each MSP that has a party. For the MSP with no party (such as Canavan, Dennis) you show the string None.

SELECT name, party
      ,NVL(party,'None') AS aff
  FROM gisq.msp WHERE name LIKE 'C%'

SELECT name, party
      ,COALESCE(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'

See also

• CASE function
• COALESCE function

Language:Project:Language policy	English  • Deutsch