Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

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
0
jtuttle99
Asked:
jtuttle99
  • 2
  • 2
1 Solution
 
sjklein42Commented:
The problem is that concatenating a null string to a non-null string does not do what you want - it makes another null string.

http://msdn.microsoft.com/en-us/library/ms176056.aspx

You need to do this before your select statment

SET CONCAT_NULL_YIELDS_NULL OFF;

Open in new window

0
 
Paul JacksonCommented:
Why not just remove NAME from the where clause ?
0
 
jtuttle99Author Commented:
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.  
0
 
sjklein42Commented:
Actually, COALESCE looks absolutely perfect for this!

http://msdn.microsoft.com/en-us/library/ms190349.aspx
0
 
jtuttle99Author Commented:
Thank you!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now