More flexible/powerful search

I have some web pages that enable users to search a database.



They can type in a keyword or phrase in a text area, and select one or more checkboxes to narrow the search.



This works well, except I would like the keyword search to be a bit more flexible.



At the moment, my SQL is effectively like this:



SELECT *

FROM myTable

WHERE myFieldNames

LIKE '%texfieldVariable%'



So I'm using LIKE and the wildcard operator at the start and end of the keyword.



This is the problem:

Say I have the following text in a field:



"we are the knights who say ni!"



If the user enters "the knights", they will retrieve the record, however, if they enter "the knight who say ni", they do not get the record.



Is there any way of making this type of keyword more flexible in SQL?



Thanks

What database are you using for your backend?

maybe you can use a loop vor all search words and put the result id's into a temp table...

I'm using Access at the moment, but I will be changing to SQL Server soon - via an ADP.



Olaf - your idea sounds interesting but I'm very much a newbie and have no idea how to do this.



cheers

I think its better that someone helps you with knowledge in ASP or other windows based scripting language.

If you will be moving to MS SQL you can utilize the Full Text Searching features.