Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

NULL and Empty String in LIKE clause  MS SQL

Posted on 2011-02-27
5
Medium Priority
?
628 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

636 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