[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Need T-SQL Syntax for multiple "not like" conditions

We’re trying to pull out a list

We DO want to see records with no comments AND records with comments except where the comments contain either %TRNG% or %Training%

We just can’t figure it out. Any ideas?

SELECT <fields including COMMENTS>
FROM <Tables>

WHERE
  DELIVERIES.SERVICE_PERIOD_START >= @SERVICE_PERIOD_START
  AND DELIVERIES.SERVICE_PERIOD_START <= @SERVICE_PERIOD_START2
  AND DELIVERIES.FUND_IDENTIFIER IN (@FUND_IDENTIFIER)
  AND DELIVERIES.SERVICE IN (@SERVICE)
 AND
(DELIVERIES.COMMENTS is NULL
OR
(DELIVERIES.COMMENTS <>(N'%TRNG%')
OR DELIVERES.COMMENTS <> N'%TRAINING%'))

Also tried a variety of "NOT LIKE" "NOT CONTAINS" and not getting the desired results.
Can anyone help?
0
gberkeley
Asked:
gberkeley
  • 3
  • 3
1 Solution
 
gberkeleyAuthor Commented:
Just tried
AND
(DELIVERIES.COMMENTS is NULL
OR
(DELIVERIES.COMMENTS NOT IN (N'%TRNG%')
OR DELIVERIES.COMMENTS NOT IN (N'%TRAINING%'))

No luck....
0
 
plummetCommented:
Hello,

Try this?

WHERE
  DELIVERIES.SERVICE_PERIOD_START >= @SERVICE_PERIOD_START
  AND DELIVERIES.SERVICE_PERIOD_START <= @SERVICE_PERIOD_START2
  AND DELIVERIES.FUND_IDENTIFIER IN (@FUND_IDENTIFIER)
  AND DELIVERIES.SERVICE IN (@SERVICE)
 AND
isnull(DELIVERIES.COMMENTS,'zzz') not like N'%TRNG%'
and 
isnull(DELIVERIES.COMMENTS,'zzz') not like N'%TRAINING%'

Open in new window

0
 
gberkeleyAuthor Commented:
Thanks!  
How does this work:

isnull(DELIVERIES.COMMENTS,'zzz') not like N'%TRNG%'
and
isnull(DELIVERIES.COMMENTS,'zzz') not like N'%TRAINING%'

is 'zzz' literal or should i substitute something else?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
plummetCommented:
the 'zzz' is just a dummy thing, if the field is null then use 'zzz' instead in the comparison. It could be anything as it won't be what's displayed.

To be honest it could be empty quotes: '' but that looks so much like a double quote I just stuck a value in. It really doesn't matter here.

I hope it works for you now - should do !
0
 
gberkeleyAuthor Commented:
THANK YOU SO MUCH. I'd give you 500 points just for the speed of the response alone, but the answer was spot on and it's a snippet I will use again and again in the future.
THANKS!!!!
0
 
plummetCommented:
You're very welcome, and good luck with it all.

Regards
John
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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