Microsoft® SQL Server® 2012 Bible

188

Part II: Building Databases and Working with Data

CASE

WHEN expression1 IS NOT NULL THEN expression1

WHEN expression2 IS NOT NULL THEN expression2

WHEN expression3 IS NOT NULL THEN expression3

...
ELSE NULL
END

The following code sample demonstrates the COALESCE() function returning the fi rst non-

null value. In this case, it’s 1 + 2:

SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc');

Result:

3

COALESCE() is excellent for merging messy data. For example, when a table has partial

data in several columns, the COALESCE() function can help pull the data together. For

example, assume that a client had collected names and addresses from several databases

and applications into a single table called TempSalesContact. The contact name and com-

pany name made it into the proper columns, but some addresses were in Address1, some

were in Address2, and some were in Address3. Some rows had the second line of the address

in Address2. If the address columns had an address, then the SalesNote was a real note.

In many cases, however, the addresses were in the SalesNote column. Here’s the code to

extract the address from such a mess:

SELECT COALESCE(

Address1 + STR(13) + STR(10) + Address2,

Address1,

Address2,

Address3,

SalesNote) AS NewAddress

FROM TempSalesContacts;

For each row in the TempSalesContacts table, the COALESCE() function searches

through the listed columns and returns the fi rst non-null value. The fi rst expression

returns a value only if there’s a value in both Address1 and Address2 because a value

concatenated with a null produces a null. Therefore, if a two-line address exists, then it

will be returned. Otherwise, a one-line address in Address1, Address2, or Address3 will be

returned. Failing those options, the SalesNote column will be returned. Of course, the result

from such a messy source table still needs to be manually scanned and verifi ed.

Using the ISNULL() Function

The most common null-handling function is ISNULL(), which is different from the IS NULL

search condition. This function accepts a single expression and a substitution value. If the

source is not equal to null, then the ISNULL() function passes the value on. However, if the

source is null, then the second parameter is substituted for the null, as follows:

ISNULL(source_expression, replacement_value)

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

http://www.it-ebooks.info