So now you know how to split some text into words. Let me give you an example of how I'm putting this into use, with what I like to call a "poor man's full-text search".

Yes, it's doing a table-scan every time. Yes, it's way inefficient. But still, it returns results quickly (my tables only have a few thousand rows) and sorts the results in such a way that you get the best matches at the top.

I'll use a fictional 'Products' table as an example:

 

SELECT ProductID, ProductName
FROM dbo.SplitWords(@text) w 
   INNER JOIN Products p WITH (NOLOCK) 
      ON p.ProductName LIKE '%' + w.value + '%' 
GROUP BY ProductID, ProductName 
ORDER BY COUNT(*) DESC, LEN(ProductName), ProductName

 

So, what are we doing? Well, we're splitting the given "@text" parameter into a table comprising all the individual words in the search text. Then we're joining to our Products table where any row contains one of those words in its "ProductName" field.

Lastly, we're sorting on how many words matched, and the length of the product's name. That means we get the products with the most matches first, and of those, we get the ones that must have matched pretty closely 'coz they only have short names. On the offchance that two product names have the same length, we'll then sort alphabetically on product name.

What do you think? This is doing the job nicely for me in Comicster, since I only have small(ish) tables. One small adjustment you could make would be to order by SUM(LEN(w.value)) DESC rather than COUNT(*). That would mean that products who match longer words bubble to the top. Because let's face it - if you match "widget" then that's gotta be better than if you match "a".