Microsoft® SQL Server® 2012 Bible

190

Part II: Building Databases and Working with Data

The following code converts any blanks in the MiddleName column into nulls. The fi rst

statement updates one of the rows to a blank for testing purposes:

USE AdventureWorks

UPDATE Person.Person

SET MiddleName = ''

WHERE LastName = 'Abbas'

SELECT LastName, FirstName,

CASE MiddleName

WHEN '' THEN 'blank'

ELSE MiddleName

END AS MiddleName,

NULLIF(MiddleName, '') as MiddleNameNullIf

FROM Person.Person

WHERE LastName IN ('Abbas', 'Abel')

ORDER BY LastName, FirstName;

Result:

LastName FirstName MiddleName MiddleNameNullIf

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

Abbas Syed blank NULL

Abel Catherine R. R.

Abercrombie Kim NULL NULL

The third column uses a case expression to expose the blank value as “blank,” and indeed

the NULLIF() function converts the blank value to a null in the fourth column. To test the

other null possibilities, Catherine’s MiddleName was not affected by the NULLIF() func-

tion, and Kim’s null MiddleName value is still in place.

A common use of NULLIF() prevents divide-by-zero errors. The following expression gener-

ates an error if the variable b is zero:

a / b « Error if b is 0, otherwise a normal division result

However, you can use NULLIF() such that if the value of the b variable is 0, it results in a

NULL instead of an error, as follows:

a / NULLIF(b,0) « NULL result if b is 0,

otherwise a normal division result

Now with a 0 as the result instead of an error, you can use COALESCE()to replace it with

something more usable if needed.

Scalar Functions ................................................................................................

Scalar functions return a single value. They are commonly used in expressions within

the SELECT, WHERE, ORDER BY, GROUP, and HAVING clauses, or T-SQL code. SQL Server

includes dozens of functions. This section describes some useful functions.

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

http://www.it-ebooks.info