SQL Server 2008 - Set a value when the column is null

Question

I'm doing a SELECT statement on a SQL Server 2008 database.

SELECT Name, DOB, Address1, Address2, City, State, Zip
FROM Users

However, if any of the above columns happen to be empty for a specific row, I want to put the value NA in the column.

Normally this would return:

SMITH^JOHN, 1/1/1980, 5140 N 1ST ST, NULL, NOWHERE, WA, 98221
DOE^JANE, 5/5/1970, NULL, NULL, NULL, NULL, NULL

What I want to be returned is:

SMITH^JOHN, 1/1/1980, 5140 N 1ST ST, NA, NOWHERE, WA, 98221
DOE^JANE, 5/5/1970, NA, NA, NA, NA, NA

However, I don't want to actually update the database. I just want the SELECT statement to return this static value whenever the result is NULL.

Answer 1

You want to use the COALESCE function.

SELECT
    Name
,   DOB
,   COALESCE(Address1, 'NA')
,   COALESCE(Address2, 'NA')
,   COALESCE(City, 'NA')
,   COALESCE(State, 'NA')
,   COALESCE(Zip, 'NA')
FROM Users