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
.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With