May 1, 2008 at 4:15 pm
Hello
I have a text box on my web form where the user can enter multiple comma delimited search words. The value from this text box goes to my search stored procedure in the form of a search string.
I am able to parse this comma delimited search string and get the words into a temp table.
If there are 2 words in the temp table then this is the sql that I want
select * from Items
where (description like word1 or tagnumber like word1 or user like word1)
and (description like word2 or tagnumber like word2 or user like word2)
description,tagnumber, user or the fields of the Items table.There could be any number of words in the search string.
I tried doing this
select items.* from items
join #temptable ON (description like word or tagnumber like word or user like word)
but this gives me a result equivalent to
select * from Items
where (description like word1 or tagnumber like word1 or user like word1)
OR (description like word2 or tagnumber like word2 or user like word2)
and what I want is a result equivalent to
select * from Items
where (description like word1 or tagnumber like word1 or user like word1)
and (description like word2 or tagnumber like word2 or user like word2)
Any ideas of how to get this done with any number of search words being matched against number of column/s.
Any help regarding this is appreciated.
Thank you.
May 2, 2008 at 7:01 am
Use a function to turn the comma-delimited list into a table and then join against it. You'll get all the matches and it doesn't matter how long the list is. You can search the scripts over on the left here and there are several examples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2008 at 9:37 am
Are you saying that the search has to match ALL words in the search string, as opposed to matching ANY word in the search string?
So, if the description contains word1, but not word2, you don't want that one?
How about if the description matches word1, and the tagnumber matches word2? Would that work?
If so, your best bet is joining the two in a sub-query (derived table in From, or CTE), and then going one step up from that with Having count(*) = (select count(*) from #temptable).
Something like this:
select items.*
from items
where itemid in
(select itemid
from items
join #temptable
ON description like '%' + word + '%'
or tagnumber like '%' + word + '%'
or user like '%' + word + '%'
group by itemid
having count(*) = (select count(*) from #temptable))
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:52 am
Yup... convert the search string into a table with a function and then do the search. Much easier to work with.
"Keep Trying"
May 8, 2008 at 12:39 pm
That works. Thanks a lot for your response.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply