Extract words from search string parameter

My application has a table with a description field nvarchar(255) of food items. It contains many records with similar descriptions. The user in the VS .Net web application will enter one or more words in a Search For text box, separated by spaces. The stored procedure will use each of those words in a where clause with AND. See rough example attached.G--Applications-Genavix--RequiredTasks-1

I know how to do most of what I need. But I can use help on how to extract the individual words from the input parameter.
PATINDEX? Loop using CHARINDEX? Other?

Suggestions gratefully received.
Douglass MacLeanCTOAsked:
Who is Participating?
Scott PletcherSenior DBACommented:
First choice would be SQL Server's full-text indexing.

Second choice, if you can't do the first, is to split the descriptions to another table once, when that value is first INSERTed/UPDATEd (via INSERT and UPDATE triggers).  Then heavily index that other table, and do your searches against that table.

The final choice would be to do it as now, with a LIKE search across the entire string for all values.
Hello dmaclean2.

There's a terrific article over at SQLPerformance.com that gives several good suggestions on how to parse a string in the way you're looking for:


If the article is helpful, let us know which of the techniques you end up using.

Good luck.
Douglass MacLeanCTOAuthor Commented:
Sorry for the delay in responding. I did a variant of your "...LIKE search across the entire string for all values"

Thanks for your advice
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.