1

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?

Tom
  • 17
  • 4

3 Answers3

1

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
collapsar
  • 17,010
  • 4
  • 35
  • 61
  • That doesn't work, too. If @L0 is NULL, then the expression is also NULL again. CASE COALESCE(null, '/') WHEN '/' THEN NULL ELSE null END – Tom Feb 19 '15 at 09:05
  • As i need to inlcude the command in an insert statement, i can't use your proposal in that way. Anyway, Christian's answer brought me on the right track, i was able to fix my problem with his (and also the help of you other guys) answer. Thank you! – Tom Feb 19 '15 at 10:50
1

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, '/')

Christian
  • 1,250
  • 9
  • 12
  • i already tried NULLIF and COALESCE, but it's the same problem: the expression is not allowed to be NULL – Tom Feb 19 '15 at 08:07
  • The expression is only not allowed to be a `NULL` constant. So only if you (or your SQL code generation) write literally `NULLIF(NULL, '/')` you will get the error. This is because SQL Server cannot determine the type of the return value if you pass `NULL` as a constant. – Christian Feb 19 '15 at 09:30
  • exactly. But how to fix it? – Tom Feb 19 '15 at 09:44
  • I don't understand. What is there to fix. Can't you just **not** pass a `NULL` constant? Is it because you generate the SQL string? If so, you can just cast the value e.g. `NULLIF(cast(NULL as ), '/')` where ``should be the data type of your input. But as I understand your example, you are not passing `NULL` as a **constant** but as the value of your `@LO` variable in which case there is no problem. – Christian Feb 19 '15 at 10:12
  • Almost fixed my problem...this here did the trick: CAST(NULLIF(@L0, '/') AS NUMERIC(18)) Thank you! – Tom Feb 19 '15 at 10:46
0

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

CASE COALESCE(@L0, '/') WHEN '/' THEN NULL ELSE @L0 END
TobyLL
  • 2,098
  • 2
  • 17
  • 23