Tip of the Day : Similarities and Differences Between MONEY and SMALLMONEY Data Types

SQL Server Helper - Tip of the Day

Example Uses of the 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.

Here are a few uses of the ISNULL system function:

Usage #1 : Generate a Comma-Delimited String


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