CODEWITHSUNDEEP
sql-server
AJ.
AJ.

Reputation: 11260

Changing a null string to an empty string in select statment

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?

Upvotes: 24

Views: 67694

Answers (3)

Frank Kalis
Frank Kalis

Reputation: 1352

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

Syntax: 

ISNULL ( check_expression , replacement_value )

Example:

Select ISNULL(myfield1,'') from mytable1

Upvotes: 51

Alex
Alex

Reputation: 31

SELECT DISTINCT ISNULL(columnname, '') FROM table WHERE ...

Upvotes: 3

Oded
Oded

Reputation: 499382

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.

Upvotes: 13

Related Questions