CODEWITHSUNDEEP
sqlsql-server
Pryach
Pryach

Reputation: 417

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

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.

Upvotes: 6

Views: 63922

Answers (7)

Sandeep TK
Sandeep TK

Reputation: 41

Handling NULL In SQL Server

What Is NULL ?

  • NULL means no result or unknown.
  • NULL is not equal to NULL itself.
  • A value of NULL is different from an empty or zero value.
  • NULL is the smallest value in the sorting order.
  • NULL are considered to be equal when the group by is executed.
  • If a column in the Group By clause contains rows with NULL, then these will be grouped into one group.

Handling Null In SQL Server With Examples

Upvotes: 0

haroonxml
haroonxml

Reputation: 366

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

Upvotes: 2

Nudier Mena
Nudier Mena

Reputation: 3274

In sql server 2008 there are two functions to replace NULL values with another values

1. ISNULL function required two parameters: the value to check and the replacement for null values

ISNULL(value,replacement)

2.COALESCE function works a bit different COALESCE will take any number of parameters and return the first non-NULL value , I prefer COALESCE over ISNULL 'cause meets ANSI standarts, while ISNULL does not.

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

I hope this work for you.

Upvotes: 6

Charleh
Charleh

Reputation: 14012

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 non-null value

There is a detailed article describing the differences here:

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

Upvotes: 5

sorpigal
sorpigal

Reputation: 26086

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 is exactly like isnull except that it can take more than two arguments. The arguments are checked from left to right and the first non-null value is returned. Values must be of the same type.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65244

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

Upvotes: 2

Joseph Sturtevant
Joseph Sturtevant

Reputation: 13360

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

Upvotes: 20

Related Questions