Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Similarities and Differences Between LEN and DATALENGTH Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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.

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]

Back to Tip of the Day List Next Tip