ISNULL System Function¶

The ISNULL system function replaces NULL with the specified replacement value. The return data type is the same type as the first parameter. The syntax of the ISNULL system function is as follows:

ISNULL ( <check_expression>, <replacement_value> )

The <check_expression> parameter is the expression to be checked for NULL and can be of any type. The <replacement_value> parameter is the expression to be returned if <check_expression> is NULL. The <replacement_value> must be of a type that is implicitly convertible to the type of the <check_expression> parameter. The value of <check_expression> is returned if it is not NULL; otherwise, the value of the <replacement_value> is returned after it is implicitly converted to the type of the <check_expression> parameter, if the types are different.

Sample Uses of the ISNULL System Function¶

Here are a few uses of the ISNULL system function:

Usage #1 : Generate a Comma-Delimited String¶

DECLARE @CSV     VARCHAR(MAX)

SELECT @CSV = ISNULL(@CSV + ',', '') + [UserName]
FROM [dbo].[Users]

Usage #2 : Get Full Name from an Optional First Name and an Optional Last Name¶

SELECT ISNULL([LastName] + ', ', '') + ISNULL([FirstName], '') AS [FullName]
FROM [dbo].[Users]

Usage #3 : Listing NULL Values at the End of an Ordered Result Set¶

SELECT [FirstName], [LastName], [Salary]
FROM [dbo].[Employees]
ORDER BY ISNULL([Salary], 9999999) ASC

Usage #4 : Return the First Non-NULL Value from a List of Columns¶

SELECT ISNULL(ISNULL([PhoneNumber], [CellNumber]), [OfficeNumber]) AS [Contact Number]
FROM [dbo].[Customer]