Geek Answers Handbook

SQL Server 2008 - set value when column is zero

I am doing a SELECT statement in a SQL Server 2008 database.

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

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

This usually returns:

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

I want to return:

 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 do not want to update the database. I just want the SELECT return this static value whenever the result is NULL .

+4
source share
6 answers

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
+15
source

Try:

 ISNULL(expression, value_if_expression_is_null)

As others have pointed out, COALESCE is also an option:

 COALESCE(expression, expression2, expression3)

which returns the first nonzero value

There is a detailed article describing the differences here:

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

+5
source

In SQL Server 2008, there are two functions for replacing NULL values ​​with other values.

1. ISNULL requires two parameters: the value to check and the replacement of zero values

ISNULL (value, replacement)

2.COALESCE function works a little differently COALESCE will take any number of parameters and return the first value other than NULL, I prefer COALESCE over ISNULL, because it complies with ANSI Standards, while ISNULL does not.

COALESCE (value1, value1, value3, valueN, replacement)

I hope this work is for you.

+5
source 
 SELECT Name, DOB, (CASE WHEN Address1 IS NULL THEN 'NA' ELSE Address1 END) AS Address1, (CASE WHEN Address2 IS NULL THEN 'NA' ELSE Address2 END) AS Address2, ... FROM Users
+2
source

Use isnull :

 SELECT Name, DOB, isnull(Address1, 'NA') as [Address1], isnull(Address2, 'NA') as [Address2], isnull(City, 'NA') as [City], isnull(State, 'NA') as [State], isnull(Zip, 'NA') as [Zip] FROM Users

You can also use coalesce , which exactly matches isnull , except that it can take more than two arguments. The arguments are checked from left to right and the first nonzero value is returned. Values ​​must be of the same type.

+1
source 
 SELECT Name, DOB, Address1, coalesce(Address2,'NA'), coalesce(City,'NA'), coalesce(State,'NA'), coalesce(Zip,'NA') FROM Users
+1
source

More articles:


All Articles