gberkeley
asked on
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_
AND DELIVERIES.SERVICE_PERIOD_
AND DELIVERIES.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?
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_
AND DELIVERIES.SERVICE_PERIOD_
AND DELIVERIES.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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
How does this work:
isnull(DELIVERIES.COMMENTS
and
isnull(DELIVERIES.COMMENTS
is 'zzz' literal or should i substitute something else?
How does this work:
isnull(DELIVERIES.COMMENTS
and
isnull(DELIVERIES.COMMENTS
is 'zzz' literal or should i substitute something else?
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 !
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 !
ASKER
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!!!!
THANKS!!!!
You're very welcome, and good luck with it all.
Regards
John
Regards
John
ASKER
AND
(DELIVERIES.COMMENTS is NULL
OR
(DELIVERIES.COMMENTS NOT IN (N'%TRNG%')
OR DELIVERIES.COMMENTS NOT IN (N'%TRAINING%'))
No luck....