NULL and Empty String in LIKE clause MS SQL
Posted on 2011-02-27
I have following SQL syntax and I'm having problem getting right results.
SELECT ImageID, Name, Description, Description2, Priority
WHERE (deleted NOT IN (1)) AND (ImageID + ' ' + Name + ' ' + Description + ' ' + Description2 LIKE N'%' + '52990' + N'%')
ORDER BY Priority
The results changes depends on filed name "Name" value although I don't use the value in my condition.
IF filed name "Name" is NULL, I get no results
If filed name is emply string, I get results.
Filed name "Name" doesn't matter what it is because ImageID has value of '52990'.
However, it matters if "Name' is Null.
1. field values-- ImageID= 52990, Name=Null
results - no results
2. field values-- ImageID= 52990, Name=' '
results - 52990
How do I prevent from affecting result when filed name "Name' is Null?
I'm using MS SQL Express 2008