I was going to post that the result table and the related methods for this code article are nearly identical to what full-text-search (FTS) does but I see that folks beat me to it. 🙂
I've not worked with FTS before so I don't know if this article brings anything extra to the table (yet) and I haven't tested the code in this article (yet) BUT... the article appears to be a reasonable study of how FTS may work behind the scenes to build the "index table" and how it uses special tables for "noise words", etc.
Why is that good? Heh... because SQL Server isn't the only database in town. 😀 This article shows how it could be done either in other database engines which may not have an FTS capability or in a "custom database".
--Jeff Moden
Change is inevitable... Change for the better is not.