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?
gberkeleyAsked:
Who is Participating?
 
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:
Just tried
AND
(DELIVERIES.COMMENTS is NULL
OR
(DELIVERIES.COMMENTS NOT IN (N'%TRNG%')
OR DELIVERIES.COMMENTS NOT IN (N'%TRAINING%'))

No luck....
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.