25

I have a SQL Server 2005 table that has a string column in which empty values are sometimes stored as NULL and other times as an empty string.

I am doing a SELECT DISTINCT on this column and I am getting all the distinct values + NULL + empty string. But what I would like is to check if the value is NULL and return an empty string instead. So the result set would be all the distinct values + empty string (if any values were null or an empty string).

But how can I do this in a SELECT statement?

Improve this question

3 Answers 3

Reset to default
52

Check out ISNULL() in the SQL Server Books Online.

Syntax: 

ISNULL ( check_expression , replacement_value )

Example:

Select ISNULL(myfield1,'') from mytable1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

13

Look at the Coalesce function. It returns the first non null value passed in.

COALESCE( myValue , '' )

This will return myValue if it is not null, or an empty string (''), if it is.

It is less verbose than using many ISNULL() and IF clauses and as such can be easier to read.

Improve this answer

2 Comments

A big difference between COALESCE and ISNULL is that the return type for COALESCE is determined by datatype precedence. So SELECT COALESCE(null, 2, current_timestamp) will return the value 1900-01-03 00:00:00.000, and SELECT COALESCE(null, 'a', current_timestamp) results in a type conversion error.
Why would you need to use ISNULL() with an IF statement?
3 
SELECT DISTINCT ISNULL(columnname, '') FROM table WHERE ...
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.