CODEWITHSUNDEEP
sqlsql-server
DubDub
DubDub

Reputation: 1377

Does the SQL'%' wild card character capture null values?

Recently I've come across a problem with a query that isn't returning everything that it's expected to return. Part of the query which selects by a condition is as follows: 

AND field LIKE 
    CASE WHEN @val = 1 THEN
        '%' 
    ELSE
        'N'
    END

Now, when @val is 1, I'd expect this piece of code to essentially do nothing, as in the condition to basically accept any value what so ever, including null values.

Am I wrong about this? And if so, does anyone have a solution? I was thinking something along the lines of 

AND field LIKE 
    CASE WHEN @val = 1 THEN
        '%' OR ISNULL(field)
    ELSE
        'N'
    END

However SQL doesn't work like that, but that's basically what I wish to accomplish.

Thanks all, and sorry if this is a duplicate, I couldn't find an answer.

Upvotes: 2

Views: 2017

Answers (3)

Jason W
Jason W

Reputation: 13179

Based on what you're trying to accomplish, it seems your query could be optimized to this:

AND (@val = 1 OR field = 'N')

There doesn't seem to be a reason for the LIKE.

UPDATE

Since you are trying to understand the behavior of LIKE and CASE moreso than working with existing queries, here are some variations of the accepted answer.

To use CASE within the LIKE, you have to use something like COALESCE to handle the null case as well.

COALESCE(Field, '') LIKE (CASE WHEN @val = 1 THEN '%' ELSE 'N' END)

Otherwise, you can use the LIKE within the CASE (like accepted answer), but probably personal preference that this seems easier to read:

1 = (CASE WHEN @val = 1 OR Field LIKE 'N' THEN 1 ELSE 0 END)

Upvotes: 7

Sachin
Sachin

Reputation: 2148

Try this (assuming that field is varchar or nvarchar) - 

AND ISNULL(field,'') LIKE 
    CASE WHEN @val = 1 THEN
        '%'
    ELSE
        'N'
    END

Upvotes: 1

Amy B
Amy B

Reputation: 110071

field LIKE '%' does not match null. CASE expressions must return a single type of result, I like int in most of mine.

AND 1 = CASE
    WHEN @val = 1 THEN 1
    WHEN field like 'N' THEN 1
    ELSE 0
  END

Upvotes: 2

Related Questions