Yet another simple question

Question

Post reply

Yet another simple question

Alex Thomas

Ten Centuries

Points: 1120
More actions
May 5, 2005 at 11:14 am

#66265

hi people,
having some problem with usnig NOT LIKEs and ANDs
1st question is there an IN LIKE command so you could say WHERE x IN LIKE ('B%', 'C%') etc?

2nd question:
ihave some code which i have shortened thus:
SELECT P.PERINDEX,
SUM(CASE WHEN BKI.RexType = 'R'
AND (bki.mrwid <> 'R' AND
bki.mrwid NOT LIKE 'R0%' AND
bki.mrwid NOT LIKE 'R1%' AND
bki.mrwid NOT LIKE 'R2%') THEN BKI.BkiNumber ELSE 0 END)
FROM X, etc etc

It brings back results as if the NOT LIKEs weren't there. the same happens if i stick them in the where clause.

However, I have a query that uses the same but
(bki.mrwid <> 'R' OR
bki.mrwid LIKE 'R0%' OR
bki.mrwid LIKE 'R1%' OR
bki.mrwid LIKE 'R2%')
and this brings back all records which have these R, R01 and R02 codes fine.
The first query i want to bring back all these results except for where this field is R, or R1xxx or R2xxx and so on.

Any suggestions?

Alex

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

mrwid LIKE 'R[0-9]%' OR mrwid = 'R' -- That will bring what you don't want

If you are able to describe what you want in terms of positive patterns use that, otherwise you can use

mrwid not LIKE 'R[0-9]%' and mrwid <> 'R'

* Noel

Vladan SSC-Insane Points: 22072 More actions · Answer 2

... or maybe a simple LEFT(mrwid,1) <> 'R' would do the trick? Possibly with some treatment of NULL values, depending on what your needs are and whether NULLs are allowed.

/edit/ What if you use parentheses in your original query? Does this help?

AND (bki.mrwid <> 'R' AND (bki.mrwid NOT LIKE 'R0%') AND

(bki.mrwid NOT LIKE 'R1%') AND

(bki.mrwid NOT LIKE 'R2%'))

Antares686 SSC Guru Points: 125444 More actions · Answer 3

To clairfy the previous this should do it.

SELECT

P.PERINDEX,

SUM(CASE

WHEN

BKI.RexType = 'R' AND

bki.mrwid != 'R' AND

bki.mrwid NOT LIKE 'R[0-2]%'

THEN

BKI.BkiNumber

ELSE

0

END)

FROM X, etc etc

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

To answer your first question...regarding Like in (...).

While you can't do this syntax directly, you can get the same results by putting all the values in the "IN" into a table, then joining to that table using "LIKE".

From #LikeTable l

JOIN #Table t on l.Code like t.Code + '%'

Signature is NULL