StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is a solution using recursive CTEs. This actually uses two separate recursions. The first one splits the strings into tokens and the second one recursively filters the records using each token. </p> <pre><code>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) </code></pre> <p>This took me about 15 seconds to search a table of 10k records for the search string 'this is a test field'.. (the more words in the string, the longer it takes.. )</p> <p><strong>Edit</strong><br> If you want a fuzzy search i.e return closely matching results even if there wasnt an exact match, you could modify the last line in the query to be -<br> <code>select * from (select max(ind) as ind, myfield from filter group by myfield) t order by ind desc</code></p> <p>'ind' would give you the number of words from the search string found in myfield.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload