Search code examples
sqlnullsql-like

SQL: Like '%' + NULL + '%' - what will match to this?

I have this code:

where (object like
(case when df.RuleType = 'Contains' then '%' + df.[Pattern A] + '%' end)
And object like
(case when df.RuleType = 'Contains' then '%' + df.[Pattern B] + '%' end)
OR object in (case when df.RuleType = 'Equals' then df.[Pattern A] end))

In some cases, the input 'Pattern B' will be NULL. How will SQL handle this? How I would like it to be handled is that if object contains Pattern A and Pattern B is null, then the where clause returns true (assuming RuleType is also contains). If RuleType is equals, it just skips to the OR bit and if object is exactly the same as Pattern A it returns True.

However, I am not getting all the output I would expect if SQL was handling it this way (some entries that should return TRUE aren't).

Solution 
  • (case when df.RuleType = 'Contains' then '%' + ISNULL(df.[Pattern A], object) + '%' end)

    However this is not a particularly wonderful way - but I suspect it will work albeit with performance issues as already commented.