February 24, 2009 at 2:42 am
Hi everybody,
Anybody knows of a SQL technique that allows the use of a wild card with IN operator, without using any functions?
Ex.
SELECT *
FROM person
WHERE personname IN ('%mar%', '%jo%')
Expeted Reults include: Mario, Mary, Joseph etc...
Thanks for your input!
February 24, 2009 at 2:50 am
You cannot add wildcard % to a IN() clause!
IN clause compares value but LIKE command looks for pattern. You may need to use LIKE only!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 24, 2009 at 2:55 am
Another suggestion is to look at CONTAINS.
Max
February 24, 2009 at 2:59 am
I guess that won't work, instead use OR operator
SELECT *
FROM person
WHERE personname LIKE '%mar%'
OR personname LIKE '%jo%'
John Smith
February 24, 2009 at 3:05 am
Yes i was aware that it is not possible but I was wondering if there is some workaround around this.
Thanks a lot for your input
February 24, 2009 at 3:15 am
if you want to avoid using lots and lots of ORs or you don't know how many strings you need to check before you run the query then try a combination of lookup table and patindex:
select * into person from (
select 'mary' as personname
union all
select 'joe' as personname
union all
select 'andrew' as personname
union all
select 'mark' as personname) P1
select * into lookuplist from (
select 'ma' as lu
union all
select 'jo' as lu) L1
select *
from Person, lookuplist
where patindex('%'+lu+'%', personname) > 0
February 24, 2009 at 5:40 am
I think the union method is the way to go. Probably the most efficient method of the lot.
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply