I need to build a query that will return records from a table where a text field, which often consists of a string of several words, contains within it a single word that is similar to some other supplied word. I know that Soundex is limited, but it is probably good enough for my purposes. Take for instance the following string:
"This string has several words"
The set of Soundex values for these five words looks like this:
"T200 S365 H200 S164 W632"
...but using the entire text string as the argument for the Soundex function returns only the value of the first word, T200, so I don't have a ready-made function to actually produce the above list of all five Soundex codes for searching.
Again, my goal is to return only those records where one or more words in the entire string contains the same Soundex value as the single word supplied for the search. In this sample case, if supplied with the word "Haze", which has a Soundex value of H200, I would have a match because the third word, "has", is the same Soundex value.
I am working in SQL Server 2005.