Find the varchar field containing all words from another line

Question

trying to execute a small stored procedure without having to add freetext indexing just for that (SQL Server 2008)

Basically, I want to find all entries where a certain field contains all the words from the parameter.

So, if there is “This is a test field” in the field, and the parameter for my SP will be “this test field”, it will return it as if this parameter was “field this test”.

The table is very small (4000) and the load will be low, so performance is not a big deal. Right now, the only solution I can think of is to separate both rows with a table function and go from there.

Any simpler idea?

Thanks!

Answer 1

Here is a solution using recursive CTEs. It actually uses two separate recursions. The first breaks the lines into tokens, and the second recursively filters the records using each token.

declare @searchString varchar(max), @delimiter char; select @searchString = 'This is a test field' ,@delimiter = ' ' declare @tokens table(pos int, string varchar(max)) ;WITH Tokens(pos, start, stop) AS ( SELECT 1, 1, CONVERT(int, CHARINDEX(@delimiter, @searchString)) UNION ALL SELECT pos + 1, stop + 1, CONVERT(int, CHARINDEX(@delimiter, @searchString, stop + 1)) FROM Tokens WHERE stop > 0 ) INSERT INTO @tokens SELECT pos, SUBSTRING(@searchString, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS string FROM Tokens OPTION (MAXRECURSION 25000) ; ;with filter(ind, myfield) as ( select 1,myfield from mytable where myfield like '%'+(select string from @tokens where pos = 1)+'%' union all select ind + 1, myfield from filter where myfield like '%'+(select string from @tokens where pos = ind + 1)+'%' ) select * from filter where ind = (select COUNT(1) from @tokens) 

It took me about 15 seconds to search for a table of 10 thousand records for the search string "this is a test field" .. (the more words in the string, the longer it takes.)

Edit
If you want a fuzzy search to return closely comparable results, even if there was no exact match, you could change the last line in the query -
select * from (select max(ind) as ind, myfield from filter group by myfield) t order by ind desc

'ind' will give you the number of words from the search bar found in my field.

Answer 2

If efficiency is not a big problem, why not go with a bit of dynamic SQL. Sort of:

 create procedure myproc (@var varchar(100)) as set @var = '%' + replace(@var, ' ', '%') + '%' exec ('select * from mytable where myfield like '''+ @var + '''')