Solved

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

Posted on 2013-06-19
5
250 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

13 Experts available now in Live!

Get 1:1 Help Now