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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

nemws1Database 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

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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lcohanDatabase AnalystCommented:
If the table column(s) allow NULLs then you can if not....you can-not.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.