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 valu开发者_JAVA技巧es + 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?

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

Syntax:

ISNULL ( check_expression , replacement_value )

Example:

Select ISNULL(myfield1,'') from mytable1

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.

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