Using Soundex in SQL Server to match ONE word within string of MANY words.

Thanks, angelIII, for your quick response.  I am admittedly a non-expert in SQL Server (much more proficient in Access and working my way up the SQL learning curve) so I have had to scratch my head over your answer a bit, especially since the code snippet seems to end prematurely with a colon.  

The first hurdle was in finding a version of ParmsToList that contained the second argument (delimiter).  There is another version floating around that only has a single argument, and that, of course, did not work very well.

I have been able to run ParmsToList, supplying a string of words as the argument, and getting a table in return, but this does not get me where I want to go.  I will need a little more handholding to get the actual results I am after.  Specifically, I need to start with a table that contains two columns, RowID and RowText, as shown in the code snippet (to retain spacing).  

I need to know if it is possible to construct a query (view), such that if I know I am looking for Soundex matches for the word "Haze", the query will return three rows, each having only the RowID field.  The values returned would be A, B and D, since those rows contain words (has, hose and he's) whose Soundex code (H200) matches that of Haze.

RowID    RowText
-----    -------
A        This string has several words
B        The garden hose is too short
C        This will not match
D        I think he's right
                                              

1:
2:
3:
4:
5:
6:

OK.  After a few hours of head scratching I did finally figure out how to do this using your two functions.  Your TextToSoundEx function needed to be modified such that the @text parameter took the place of the string 'This string has several words' as the first argument of ParmsToList, and with that in place, I was able to get the desired output using:

SELECT    RowID
FROM         MyTable
WHERE     (dbo.TextToSoundEx(RowText) LIKE '%' + SOUNDEX('haze') + '%')

It is pretty slow, taking about 15 seconds to return 42 records out of about 14,000, but it works.  Any help on streamlining this would be appreciated, but I am glad to at least have a working system.  Thanks!

glad I could help.

for the efficiency part, I would create an additional column in the table, and on insert/update of the record, update the soundex value immediately ...

So you are suggesting a calculated third column in MyTable (say, RowSoundex?) that would contain the Soundex values for the words in RowText, storing "T200 S365 H200 S164 W632" in the same record as "This string has several words"?  I suppose I would add these values now for all existing records, just using an update query, and then create a trigger to manage the new and updated records in the future...  That will be my first trigger!  Any pointers on how to do that?  I'm sure it's covered in BOL, but maybe you have a link to a good beginner's tutorial or something?  Thanks again.