In 'LIKE' operator needs to select 'NULL'(DB) values

Question

Hi all,

This is a one typical Question asked my boss..

Q: how to select the value which are nulls in Database using LIKE operator..

I tried like below:

select c.ClaimID from claim c
where c.InjuredPartyFirstName like (''+coalesce(c.InjuredPartyFirstName,'')+'') 

But this also not working........

Please help some one

Thanks in Advance... vinay k

Answer 1

Filtering by NULL values is performed like so:

select c.ClaimID 
from claim c 
where c.InjuredPartyFirstName IS NULL

Answer 2

Why not use

select c.ClaimID from claim c where c.InjuredPartyFirstName IS NULL 

If this is some kind of strange test, try

select c.ClaimID from claim c 
where ISNULL(c.InjuredPartyFirstName, '') like ''

Answer 3

So the correct answer to your boss's trick question is that you can't do it directly with LIKE unless you want to slow the query down by coalescing the null values first. And then explain to him/her why a NULL is NULL and should not be capable of having a LIKE applied directly to it.

Edit: maybe this is just a test by your boss to filter the jedi's out from the pretenders?