| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2005-11-16 : 08:22:19
|
| Is there any way to allow users to search for multiple keywords without using dynamic SQL?eg they type: "fish store" and it searches "%fish%" AND "%store%" but passing only a single parameter to the stored procedure.I would like to avoid using full-text indexing if at all possible as it doesn't seem to work very well on SQL 2000 and always causes problems with noise words etc...Thanks,Nick.. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-16 : 08:55:02
|
Do you need something like this?Declare @t table(data varchar(20))insert into @t select 'this is test' union all select 'there is fish stored' union all select 'fish and store'select data from @tDeclare @s varchar(20)set @s='fish store'select data from @t where data like '%'+substring(@s,1,charindex(' ',@s,1)-1) +'%' and data like '%'+ substring(@s,charindex(' ',@s,1)+1,len(@s)) + '%'MadhivananFailing to plan is Planning to fail |
 |
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2005-11-16 : 10:01:57
|
| Thanks, but wouldn't that only work if they supply exactly 2 words? I need it to work for any number of words from 1 to 5 words say... |
|
|
|
CokoBWare
Starting Member
6 Posts |
Posted - 2005-11-16 : 11:05:44
|
This approach seemed to work for when I needed to search multiple keywords passed by a search phrase to the stored procedure...First, you need to prep your search phrase:DECLARE @vchNewSearchPredicates VARCHAR( 5000 )DECLARE @vchSearchPhrase VARCHAR( 5000 )SET @vchNewSearchPredicates = REPLACE(LTRIM(RTRIM(@vchSearchPhrase)),' ',' AND ') @vchSearchPhrase is the keywords separated by spaces (or whatever you wanted to be the separator, like a comma ',') to @vchNewSearchPredicates will be the final input to our magic where clause. The operator used in this example is AND .Then, in your SELECT statement, use this WHERE clause:CONTAINS(your_field_here, @vchNewSearchPredicates) Check SQL Books Online for the documentation for CONTAINS...I hope this puts you on the right track to solving your problem.Cheers,Cory |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2005-11-16 : 11:18:39
|
| I thought you could only use CONTAINS if you're using full text indexes on the columns concerned? In the past, full text indexing has caused me lots of grief and I was trying to find a solution that avoids it (the database is quite small and doesn't really warrant it. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-16 : 12:06:00
|
| Can you parse the search words into a (temp) table and do a join to that...including a % in the on "a.col like b.col" statement?Search here for parsing strings by delimiter (comma or space) |
|
|
|
CokoBWare
Starting Member
6 Posts |
Posted - 2005-11-16 : 12:32:03
|
| Ah yes CONTAINS is a problem because it uses full-text. Apologies... my brain is not in full gear today.However I can offer a consolation prize... perhaps you would consider using Regular Expressions to do your search? Please read the article I wrote on RegEx in T-SQL... http://www.sqlteam.com/item.asp?ItemID=13947The code in the article is not tuned for performance. Please read the comments section and use the performance changes a reader suggested, as they improve the performance noticably. This is all accomplished without Full-Text indexing.Cheers,Cory Koski |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-17 : 00:19:03
|
| Have your @SearchString as comma delimited data and then try thisSelect Columns from yourTable where '%,'+@SearchString+'%' like '%'+yourCol+'%'But you can consider the approach given by AndrewMurphy which is more efficientMadhivananFailing to plan is Planning to fail |
|
|
|
|
Search using multiple keywords without full-text?