SQL ISNULL Function

The ISNULL function replaces NULL values with a specified value.

This function is a short-hand for a CASE expression.

ISNULL is used to replace NULL with a more meaningful value.

Example

#

This example replaces NULL with the a double-dash value.

SELECT CompanyName, ISNULL(Fax, '--') AS Fax
  FROM Supplier
Result:  29 records
CompanyName Fax
Exotic Liquids --
New Orleans Cajun Delights --
Grandma Kelly's Homestead (313) 555-3349
Tokyo Traders --
Cooperativa de Quesos 'Las Cabras' --

As you can see, when Fax is NULL, it is replaced with '--'.


Syntax

Syntax of the ISNULL function.

ISNULL(value, replacement-value)

value -- a value or column name (which is checked for NULL).

replacement-value -- a value to replace the NULL value with.

More Examples

ISNULL.

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers with contact information. Include fax, but for those without fax use phone number instead.
SELECT CompanyName, ISNULL(Fax, 'Phone: ' + Phone) AS Contact
  FROM Supplier
Result:  29 records
CompanyName Contact
Exotic Liquids Phone: (171) 555-2222
New Orleans Cajun Delights Phone: (100) 555-4822
Grandma Kelly's Homestead (313) 555-3349
Tokyo Traders Phone: (03) 3555-5011
Cooperativa de Quesos 'Las Cabras' Phone: (98) 598 76 54

You may also like



Guides


vsn 3.1