How do I create an index/broad matching problem.

[wheel]

I'm creating an application to serve results based upon matching queries. So I'll pass a query string, and I need to pull matching results from a database. I'm seeking your suggestions on how to do this.

For example, database has two fields; 'query string' and 'display results'. Now if I pass the database an input query string, I want it to return all 'display results' that match the input query string. The twist is that I need a 'broad match' to the query.

So, if I pass 'red widgets alabama', I need to produce display results from records that have a query string of any combination, in any order, of 'red', 'widgets', and 'alabama'. i.e. 'widgets red', 'alabama widgets', etc.

I think my best bet is to create an index (though I don't know how to do that off the top of my head). Looking for some emphasis on speed of returning results.

Any suggestions on how to tackle this problem?

TIA

[httpwebwitch]

you can create a basic SQL statement like

SELCT * WHERE
name LIKE '%red%'
OR name LIKE '%alabama%'
OR name LIKE '%widgets%'

Then there are several ways those results can be cached

the statement above will match "falabaman" and "greddo" and "twidgetsoap"

[Dijkgraaf]

Are you always looking to match full words?

If so you could create another table that breaks the query string into individual words which has a pointer the record that contains that word in the query string.

And index on the words in word table would improve performance.