Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-19
5
Medium Priority
?
334 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:sir plus
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1800 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 200 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:sir plus
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
1

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
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…

877 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