Solved

NULL and Empty String in LIKE clause  MS SQL

Posted on 2011-02-27
5
619 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 39
string fuctions 4 25
SQL Improvement  ( Speed) 14 26
SQL - Use results of SELECT DISTINCT in a JOIN 4 13
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

806 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