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

Question

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.

Add some sample table data and the expected result - as formatted text, not images. — jarlh, Sep 20 '18 at 13:12
`ISNULL`, on SQL Server, requires 2 parameters. The first is the NULLable value,. and the second is the value to return if the first has a value of `NULL` (this second value could also, have a value of `NULL`, which would return `NULL`). — Thom A, Sep 20 '18 at 13:17
`NULL` essentially has no value, so it can't be equal to or like anything, even a wildcard. — Shawn, Sep 20 '18 at 13:34

Jason W · Accepted Answer · 2018-09-20T15:45:50.493

7

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)

edited Sep 20 '18 at 15:45

answered Sep 20 '18 at 13:15

Jason W

13,026
3
31
62

Exactly. Check out this answer to a similar question: https://stackoverflow.com/a/18683575/44522 – MicSim Sep 20 '18 at 13:17
1

Except, maybe, the original author ***wanted*** to exclude NULLs? ;) – MatBailie Sep 20 '18 at 13:20
1

Original undocumented intent is very hard to decipher :) – Stuart Ainsworth Sep 20 '18 at 13:22
2

Perhaps. The intent code shared seemed to be that the author thought NULL should be included. Otherwise, original code would have worked as it already excluded NULL. – Jason W Sep 20 '18 at 13:23
I don't expect you all to read my life story, but I stated that I expected the query to include null values, hence I didn't want to exclude nulls. However, that's why I asked if the % wildcard did exclude nulls. :) – DubDub Sep 20 '18 at 13:27
Updated answer with variations to use `CASE`/`LIKE` as you mentioned. – Jason W Sep 20 '18 at 15:46

score 2 · Answer 2 · answered Sep 20 '18 at 13:13

2

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

answered Sep 20 '18 at 13:13

Amy B

108,202
21
135
185

Thanks for answering the question and providing a solution to fix my problem. – DubDub Sep 20 '18 at 13:28
`field like 'N'` is essentially the same as `field = 'N'`, though there are some slight differences between SQL `LIKE` and `=`. If the intent was for `field` to be 'N', then an `=` would be clearer than a `LIKE`. `LIKE` in SQL usually implies that the OP would want 'N', 'No', 'Ain't Gonna Pick Me' to all match the condition. – Shawn Sep 20 '18 at 13:39
Sure, `=` is clearer than `LIKE` for this match. I felt that drawing attention to that was drawing attention away from other important knowledge. I wanted to focus on how LIKE works with nulls and how to write clear CASE expressions that return a single type as required. That knowledge was requested. – Amy B Sep 20 '18 at 14:49
NOTE: In my above comment, I should have added that _**IN THIS INSTANCE**_ they are essentially the same. However, `LIKE` and `=` can behave _very_ differently, especially where trailing spaces, collation and wildcard characters in the data come into play. – Shawn Sep 20 '18 at 17:29
It's kind of a ho-hum pattern of communication in stackoverflow where we talk about comments in the comments rather than about the answer. – Amy B Sep 20 '18 at 17:40
:-) True. Though comments sometimes do have some good nuggets of information for future readers. – Shawn Sep 20 '18 at 18:47

Sachin · Answer 3 · 2018-09-20T13:36:52.107

1

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

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

edited Sep 20 '18 at 13:36

answered Sep 20 '18 at 13:14

Sachin

2,152
1
21
43

[NULLIF returns a null value if the two specified expressions are equal](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql) – Peter B Sep 20 '18 at 13:21
oops its my mistake.. it is `ISNULL` and not `NULLIF` – Sachin Sep 20 '18 at 13:36

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

3 Answers3