jtuttle99
asked on
NULL and Empty String in LIKE clause MS SQL
Hello, experts
I have following SQL syntax and I'm having problem getting right results.
SELECT ImageID, Name, Description, Description2, Priority
FROM Items
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.
IE.
--search 52990
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
Thank you
I have following SQL syntax and I'm having problem getting right results.
SELECT ImageID, Name, Description, Description2, Priority
FROM Items
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.
IE.
--search 52990
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
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Paul Jackson
Why not just remove NAME from the where clause ?
ASKER
sjklein42:
That make sense!
Is it possible to use COALESCE or ISNULL? (just out of curiosity)
I will mark your post as answer after your reply.
Thank you
jacko72
I need "Name" because sometimes I use it for condition.
That make sense!
Is it possible to use COALESCE or ISNULL? (just out of curiosity)
I will mark your post as answer after your reply.
Thank you
jacko72
I need "Name" because sometimes I use it for condition.
Actually, COALESCE looks absolutely perfect for this!
http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://msdn.microsoft.com/en-us/library/ms190349.aspx
ASKER
Thank you!