SQL CASE statement with LIKE (MSG 156)

Question

This part of code is in a WHERE clause:

AND
  C = CASE
    WHEN @Variable = 'NA' THEN C
    ELSE LIKE '%' + @Variable + '%'
  END

Here's what I want: If @Variable is 'NA'(as in not applicable), then give me all results. If not, I want only C like @Variable.

The error is with the like. If I remove the = from C = CASE, there's no more error with LIKE, but the error is with CASE instead. How should I proceed?

I agree that the answers below are probably better. To address your syntax problem though: `WHEN C LIKE '%' + @Variable + '%' THEN C` instead of the `ELSE`. — shawnt00, Oct 20 '16 at 22:22
Remember that `case` is an expression. It returns a value not a boolean condition. — shawnt00, Oct 20 '16 at 22:26
Also remember to be careful with `or`. Usually you need to wrap `or`s with parentheses to get the correct precedence. — shawnt00, Oct 20 '16 at 22:28
@shawnt00 Then how can I select everything if Variable = 'NA'? — user107242, Oct 21 '16 at 00:04
Leave that part in. I was just talking about what you had as `else. — shawnt00, Oct 21 '16 at 00:10
You've written something akin to either `C = C` or `C = LIKE '%' + @Variable + '%'`, which doesn't make sense. A `case` expression can be used, as in [this](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) example, but `where @Variable = 'NA' or C like '%' + @Variable + '%'` seems simpler and clearer. — HABO, Oct 21 '16 at 01:04
Forgot to mention: `where C = C` will not return all rows if `C` can be `NULL`. — HABO, Oct 21 '16 at 12:47
@HABO I was aware of that, but I didn't feel like explaining null logic at the time. You'll notice that I avoided that problem in my answer below though. — shawnt00, Oct 22 '16 at 16:04

score 2 · Answer 1 · answered Oct 20 '16 at 21:26

2

This can be done in a simple WHERE, like so:

SELECT * FROM MyTable
WHERE (@Variable = 'OK') OR (C LIKE '%' + @Variable + '%')

answered Oct 20 '16 at 21:26

Tyler Roper

21,445
6
33
56

'OK' isn't part of the table. I'll change my question. N/A would be more appropriate. – user107242 Oct 20 '16 at 23:00
So then replace `OK` with `NA`...? – Tyler Roper Oct 21 '16 at 13:53
what does it matter if it is/isn't part of the table? a `where` doesn't have to involve a table/field AT ALL. a where clause boils down to a boolean true/false, and ANY expression which produces true/false is valid for a `where`. true = include the record, false = exclude. that means `where 1` or `where 1=0` or `where 'a'='a'` are all valid. – Marc B Oct 21 '16 at 14:19

score 1 · Accepted Answer · edited Oct 20 '16 at 21:27

1

That syntax will never work, and why go for something so complicated?

WHERE (@var = 'ok') OR (c like '%' + @var + '%')

edited Oct 20 '16 at 21:27

S3S

24,809
5
26
45

answered Oct 20 '16 at 21:27

Marc B

356,200
43
426
500

'OK' isn't part of the table. I edited my question and replaced OK with N/A – user107242 Oct 20 '16 at 23:02
irrelevant. it's just a string. and changing your question AFTER the fact basically invalidates all of these answers. – Marc B Oct 21 '16 at 14:29

score 0 · Answer 3 · answered Oct 21 '16 at 00:15

Ultimately what you want is OR. I recommend using that approach, as given by the other answers, however it is indeed possible to fix your syntax problem using CASE.

AND
  'Keep' = CASE
    WHEN @Variable = 'NA' THEN 'Keep'
    WHEN C LIKE '%' + @Variable + '%' THEN 'Keep'
    ELSE 'Discard'
  END

I tried to also highlight that the values being compared don't really matter nor is the ELSE strictly necessary.

SQL CASE statement with LIKE (MSG 156)

3 Answers3