CODEWITHSUNDEEP
sql-servercase-when
Raymond
Raymond

Reputation: 3452

SQL Server Query for checking for empty string and replacing with something else

I have an integration type project where my select column often has this code:

select 
CASE MyColumn
    WHEN '' THEN '&' 
    ELSE MyColumn
END

Is there a simpler approach? Sort of like coalesce with nulls? I'm finding the query really hard to read, etc.

Thanks

Upvotes: 2

Views: 7508

Answers (4)

d89761
d89761

Reputation: 1434

This should work, and preserve nulls:

SELECT COALESCE(NULLIF(MyColumn, ''), NULLIF(MyColumn + '&', ''), MyColumn)

Upvotes: 0

conan
conan

Reputation: 124

Following on from @Michael Fredrickson's comment

NULLIF(COALESCE(NULLIF(COALESCE(MyColumn,'###'),''), '&'),'###')

Replace ### with anyvalue you know won't appear in the field. Not sure it's easier to read than the case statement though.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

I guess you can try something like this... 

 COALESCE(NULLIF(MyColumn,''), '&')

Upvotes: 2

Vishal
Vishal

Reputation: 12369

You could use COALESCE, following would check null and empty string from here - 

COALESCE(NULLIF(MyColumn,''), '&')

Upvotes: 1

Related Questions