T-SQL: converting NULL values into empty string

Hi, I'd like to have empty  string values in the query results instead of NULL. What is the best way to accomplish this for multiple datatypes including datetime, numeric, int, varchar?

Thank you in advance.
Who is Participating?
PortletPaulConnect With a Mentor Commented:
I believe the point made by awking00 is that you can't insert an "empty string" into a non-string column.

Try this:
declare @d as datetime
declare @n as numeric
declare @i as int
declare @v as varchar

        cast(null as datetime) as dt_col
      , cast(null as numeric ) as nu_col
      , cast(null as int     ) as in_col
      , cast(null as varchar ) as va_col

union all

        isnull(@d, '')
      , isnull(@n, '')
      , isnull(@i, '')
      , isnull(@v, '')

Open in new window

All columns would have to be string types to accept ''

btw: whilst coalesce is v.cool I see no particular advantage in using it unless you have more than one expression to evaluate. As coalesce allows a variable number of parameters it's my view there is slightly more overhead to assess the number of parameters supplied. ISNULL would be my suggestion. But both will do the job.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
COALESCE will return the first non-NULL value it finds, so...

Declare @foo varchar(10) = NULL

Open in new window

nemws1Connect With a Mentor Database AdministratorCommented:
As Jimhorn says, COALESCE(), which is an excellent choice.

The other option is to use ISNULL(), for a single comparison.

Declare @foo varchar(10) = NULL;
SELECT ISNULL(@foo, '');

Open in new window

COALESCE() lets you do this:

Declare @foo varchar(10) = NULL;
Declare @bar varchar(10) = NULL;
Declare @baz varchar(10) = NULL;
SELECT COALESCE(@foo, @bar, @baz, '');

Open in new window

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Coalesce is the way I'd go as well but I'm not sure you can insert an empty string into a datetime and/or numeric field.
lcohanDatabase AnalystCommented:
If the table column(s) allow NULLs then you can if not....you can-not.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Five good expert comments.  I think we've nailed this one.

quasar_ee - How's it goin', eh?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.