|
Home > Archive > Microsoft SQL Server forum > July 2005 > Newbie like question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Newbie like question
|
|
| Driesen P via SQLMonster.com 2005-07-21, 9:23 am |
|
Hi
How do I run Multiple Likes in one query? Do I need to run each Like
seperately?
Thanks for any help.
| |
| Driesen P via SQLMonster.com 2005-07-21, 9:23 am |
|
Sorry. I don't think that was clear. What I meant was I need something that
is similar to:
Select * from Security..SecUser
where UserId in (123, 456, 789)
The following query does not work:
Select * from Security..SecUser
where UserId like ('%123%', '%456%', '%789%')
Is there a query that can do this?
Thanks again
| |
|
| You can use logical operators to join two tables; put the patterns you
want to match in one table, and join it to the source table. Here's an
example:
--Table with data that I want to look for a pattern in
DECLARE @Root TABLE (KeyVal int,
Root varchar(10))
INSERT INTO @Root
SELECT 1, 'ABCDEF'
UNION ALL
SELECT 2, 'DEFGHI'
UNION ALL
SELECT 3, '123ABC'
UNION ALL
SELECT 4, '123DEF'
--Table of patterns
DECLARE @LikeTest TABLE (LikeTest varchar(10))
INSERT INTO @LikeTest
SELECT 'ABC'
UNION ALL
SELECT 'DEF'
UNION ALL
SELECT 'XYZ'
--Results; note that I used DISTINCT to return a single value for each
match.
SELECT DISTINCT r.KeyVal, r.Root
FROM @Root r JOIN @LikeTest l ON r.Root LIKE '%' + l.LikeTest + '%'
HTH,
Stu
| |
| ZeldorBlat 2005-07-21, 1:23 pm |
| Alternatively:
Select * from Security..SecUser
where
UserId like '%123%'
or UserId like '%456%'
or UserId like '%789%'
Obviously, if you have a ton of them, it'll be a pain to type them all
out.
| |
|
|
|
|
|