CODEWITHSUNDEEP
sql-serverexpressioncasecoalescenullif
Tom
Tom

Reputation: 17

Case with NULL expression in SQL Server

I have a problem with this piece of code and i'm pretty sure the solution is quite easy, but i don't see it.

CASE @L0 WHEN '/' THEN NULL ELSE @L0 END

It is part of code i use to import data out of a flat file (csv), where NULL is stored as /. The problem is that @L0 can also be an input variable filled by another programm and it is possible that it might be NULL. Due to that it's not allowed to use a NULL value as expression in a CASE, the code doesn't work and i get an error message.

Can somebody tell me how to do it the right way?

Upvotes: 1

Views: 145

Answers (3)

collapsar
collapsar

Reputation: 17238

Map a NULL value to '/' or any other string:

 CASE COALESCE(@L0, '/') WHEN '/' THEN NULL ELSE @L0 END

 CASE COALESCE(@L0, 'other string') WHEN '/' THEN NULL ELSE COALESCE(@L0, 'other string') END

The 'other string' should of course never occur as regular non-null content of @L0. In case the result should be '/' on @L0 being the 'other string', take this one:

 CASE COALESCE(@L0, 'other string') WHEN '/' THEN NULL ELSE COALESCE(@L0, '/') END

Upvotes: 1

Christian
Christian

Reputation: 1260

If you would just like to replace the '/' with NULL and leave all other strings untouched, you can use the NULLIF() function e.g. NULLIF(@Lo, '/')

Upvotes: 1

TobyLL
TobyLL

Reputation: 2296

Try the following, which defaults @L0 to '/' if it is null:

CASE COALESCE(@L0, '/') WHEN '/' THEN NULL ELSE @L0 END

Upvotes: 0

Related Questions