LIKE/IN by using EXISTS? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

LIKE/IN by using EXISTS?

Author

Topic

redline9k
Starting Member

4 Posts

Posted - 2010-12-28 : 09:54:58

I have a list of criteria that should be LIKE clauses, but dont want to do this;

WHERE CS like 'Re%' OR CS like 'WH%' OR CS like 'GO%'

on and on for 15 criteria

Is this a reliable and professional way to accomplish this with alot less code? In this case fqinfo_new is a temp table with all of the criteria such as 'Re%', 'WH%', 'GO%'.


SELECT a.CS, a.OCS
FROM pipe a 
     WHERE EXISTS
     (SELECT * FROM (select CS from ##fqinfo_new) x
WHERE a.CS LIKE x.CS OR a.CS LIKE x.CS) 
GROUP BY a.CS, a.OCS

It works on my end, I am just curious if its reliable and/or professional? I taught myself SQL, so I am always trying to figure out if the method I used is good practice.

Thanks

Doug

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 10:06:01

Do you need a global temp table?
I would do it with a table variable.
SELECT a.CS
FROM pipe a
WHERE EXISTS (SELECT * from @fqinfo_new x WHERE a.CS LIKE x.CS)

or maybe
SELECT a.CS
FROM pipe a
join @fqinfo_new x
on a.CS LIKE x.CS

If this can't use an index then
WHERE left(CS,2) in ('Re','WH','GO',...)
might be better.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

redline9k
Starting Member

4 Posts

Posted - 2010-12-28 : 10:25:16

ah, ok, learn something new there about temp variables.

Ill use the table variable instead....

but the general idea of using the EXISTS is ok to get rid of "like or like or like or"? I do need the ability to use a wildcard on any one of the 15 possible variables of 2-20chr in length.

Thanks

...and I dont think it changes anything, but i didnt mean to duplicate the condition in the original post....It should be;


WHERE a.CS LIKE x.CS OR a.OCS LIKE x.CS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 10:30:47

Your where clause is a bit more complicated than it needs to be
SELECT a.CS, a.OCS
FROM pipe a
WHERE EXISTS
(SELECT * FROM @fqinfo_new x WHERE a.CS LIKE x.CS OR a.CS LIKE x.CS)
GROUP BY a.CS, a.OCS

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources