multi word search using stored procedures 2

[boro2g]

I am currently implementing a search function from an asp page where the user can input a string (restricted to about 255 chars), from which a dynamic sql statement is constructed.

Eg if they search for "a b c", the script recurses through each word and append the sql to a str which would then be sent to the db. The problem with this method is it is rather easy to hack if you know asp and the way this method works.

Ideally i would use sql server stored procedures to perform the search, but am having a problem on how to parse the user input. I want to be able to search on each word input by the user, but the number of words input is not always fixed (could be 'a b c', 'a b', 'a b c d' etc)

Does anyone know the best way to approach parsing and handling the information? (a temporary db table || send each word individually -> multiple rs's???)

Many thanks in advance.

Nick Hills

 

[JamesLean]

Check out the function in faq183-5207. This allows you to pass in a delimited list (in your case the delimiter will be a space) and return a table with one value (word) per row. You can then do your search something like:

SELECT DISTINCT t1.search_col
FROM yourtable t1 JOIN dbo.split('search for these words', ' ') t2 ON t1.search_col LIKE '%' + t2.value + '%'

--James

 

[boro2g]

thanks very much for that - works a treat

one thing, is it possible to use a search string containing non-alphabetical chars eg

dbo.split('why won't this work',' ') - ie cope with ",',-,/ etc

cheers

 

[NilsBevaart]

Whenever you would find a ' in any fieldvalue, you want to add an extra quote before it. Otherwise you would end the text selection in you TSQL code.
So you would get:

SELECT * FROM dbo.split('why won''t this work',' ')

Nils Bevaart