Solved

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

Posted on 2013-06-19
5
266 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
  • 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 48

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now