Solved

NULL and Empty String in LIKE clause  MS SQL

Posted on 2011-02-27
5
625 Views
Last Modified: 2012-05-11
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
Comment
Question by:jtuttle99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
sjklein42 earned 500 total points
ID: 34992765
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34992778
Why not just remove NAME from the where clause ?
0
 

Author Comment

by:jtuttle99
ID: 34992793
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
 
LVL 16

Expert Comment

by:sjklein42
ID: 34992803
Actually, COALESCE looks absolutely perfect for this!

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

Author Closing Comment

by:jtuttle99
ID: 34992830
Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question