Microsoft® SQL Server® 2012 Bible

189

Chapter 8: Data Types, Expressions, and Scalar Functions

8

Functionally, ISNULL() is similar to the following case expression:

CASE

WHEN source_expression IS NULL THEN replacement_value

ELSE source_expression

END

The following code sample builds on the preceding queries by substituting the string

(‘NONE’) for a null for people without a middlename:

USE AdventureWorks

GO

SELECT

FirstName,

ISNULL(MiddleName, 'None') MiddleName,

LastName

FROM Person.Person

Result:

FirstName MiddleName LastName

----------- ------------- ------------

Syed E Abbas

Catherine R. Abel

Kim None Abercrombie

...

If the row has a value in the Nickname column, then that value is passed through the

ISNULL() function untouched. However, if the nickname is null for a row, then the null is

handled by the ISNULL() function and converted to the value none.

The ISNULL() function is specifi c to T-SQL, whereas NULLIF() is ANSI standard SQL.

Using the NULLIF() Function

Sometimes a null should be created in place of surrogate null values. If a database is pol-

luted with n/a, blank, or – values where it should contain nulls, then you can use the

NULLIF() function to replace the inconsistent values with nulls and clean the database.

The NULLIF() function accepts two parameters. If they are equal, then it returns a null;

otherwise, it returns the fi rst parameter. Functionally, NULLIF() is the same as the fol-

lowing case expression:

CASE

WHEN Expression1 = Expression2 THEN NULL

ELSE Expression1

END

c08.indd 189c08.indd 189 7/30/2012 4:21:14 PM7/30/2012 4:21:14 PM

http://www.it-ebooks.info