[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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

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
sir plus
Asked:
sir plus
  • 2
  • 2
2 Solutions
 
Kelvin SparksCommented:
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
 
PortletPaulCommented:
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
 
sir plusSales ManagementAuthor Commented:
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
 
PortletPaulCommented:
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
 
Kelvin SparksCommented:
Thanks, When reading PortletPaul's response, I did hope that you'd reward him too.

Kelvin
1

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now