• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • 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 JacksonSoftware EngineerCommented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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