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 MacLeanCEO, CTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Douglass MacLeanCEO, CTOAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.