Solved

SQL/Query won't return records with Blanks when I want to retain them

Posted on 2013-06-19
5
316 Views
Last Modified: 2013-06-19
This query

SELECT STK_MASTER.STOCK_CODE, STK_MASTER.STOCK_CATEGORY, STK_MASTER.STOCK_DESCRIPTION_1, STK_MASTER.STOCK_DESCRIPTION_2FROM OURDB.dbo.STK_MASTER STK_MASTER
WHERE (STK_MASTER.STOCK_DESCRIPTION_2 Not Like '%DO NOT USE%')

Returns only records which do not contain the text DO NOT USE in the field STOCK_DESCRIPTION_2, unfortunately it also does not return fields where STOCK_DESCRIPTION_2 is blank. I need it to return the blank records as well
SQL-Issue.JPG
0
Comment
Question by:sirplus
[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 22

Accepted Solution

by:
Kelvin Sparks earned 450 total points
ID: 39258659
Try
SELECT STK_MASTER.STOCK_CODE, STK_MASTER.STOCK_CATEGORY, STK_MASTER.STOCK_DESCRIPTION_1, STK_MASTER.STOCK_DESCRIPTION_2FROM OURDB.dbo.STK_MASTER STK_MASTER
WHERE (STK_MASTER.STOCK_DESCRIPTION_2 Not Like '%DO NOT USE%') OR
 (STK_MASTER.STOCK_DESCRIPTION_2 IS NULL)

Kelvin
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 39258677
NOT LIKE '%...%'

does not locate NULLS, you still have to explicitly do as proposed above,
"or .... IS NULL"

this is confusing as heck, but in that same way that NULL cannot be = to anything
it also cannot be "like" anything either
and because "like" cannot ascertain if it is "like"; then "not like" won't work for null either
just about as clear as mud?

use the previous response's code :)
0
 
LVL 5

Author Closing Comment

by:sirplus
ID: 39261198
I gave points to PortletPaul for the fantastic explanation because I didn't get why from just the solution.
Hope that's ok with everyone..
Many thanks for working out what out IT people couldn't......
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39261210
it's fine by me :)
not sure I would claim my clear as mud explanation is fantastic - but thank you.

just note that NULL is a special case generally needing explicit conditions through:
IS NULL / IS NOT NULL
or
ISNULL()

Cheers, Paul.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39261523
Thanks, When reading PortletPaul's response, I did hope that you'd reward him too.

Kelvin
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 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