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.
| Author |
Topic |
|
toddhd
Yak Posting Veteran
55 Posts |
 Posted - 2006-01-31 : 11:00:39
|
| I have a list of items (aka Packages) stored in a table, and another table contains a list of keywords that describe each each particular package. There are several keywords associated with each package - pretty typical stuff. I need to search for a package based on those keywords. So for example:PACKAGE NAME - KEYWORDSPackage1 - red, blue, greenPackage2 - hot, cold, up, downPackage3 - spring, winter, fallMy web interface has a textbox where the user can enter the keywords and hit search. So let's say he enters "red blue hot".Now I just need to pull up all packages that are associated with those keywords. My question is how to best do this? My first thought was to use a WHERE-IN clause, like this:SELECT ...WHERE Keyword IN (@Keywords)Now if I only pass the word "blue" in (for example), it works fine. But if I pass "red blue hot" in, it doesn't work, because it thinks that is one long word. I've tried adding commas and quotes and junk, but since @Keywords is being passed as a parameter, I'm not finding the magic combo to make this work. How should I approach this problem? A function of some kind? Or am I barking up the wrong tree altogether?Any assistance would be greatly appreciated. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-01-31 : 11:04:51
|
| Use a function to split the keywors into a resultsetseehttp://www.nigelrivett.net/SQLTsql/ParseCSVString.htmlthenwhere ', ' + KEYWORDS + ',' like '%, ' + word + ',%'You would be better not havnig a string of keywords in the table but separate rows.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-02-03 : 08:18:59
|
| You can use dynamic sql. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
toddhd
Yak Posting Veteran
55 Posts |
Posted - 2006-02-03 : 09:24:13
|
quote:
Originally posted by madhivanan
Also refer where in @MyCSV herehttp://sqlteam.com/forums/topic.asp?TOPIC_ID=55210 MadhivananFailing to plan is Planning to fail
Great link, thank you! And thanks to everyone for their help with this - saved my shiny, round behind, you did... |
|
|
|
|
|
|
|
|
WHERE-IN usage - very basic search - help!