Search on a phrase returning similar values

[loydall]

Hi - lets say I have a table of people.

Each person has a description. I have set up a full text index on the description column.

Now - I want to search that column. How do I search using a phrase alowing for slight variations in what the user might enter? Bearing in mind that the results MUST include each of the saerch terms (or at least variations on each of the search terms).

So - if I search for "term1 term2 term3" it will only return records that contain each of those words (or variations). But they can appear in any order in the descrition column.

So far I've got:

SELECT FirstName, LastName
FROM tablePeople
WHERE CONTAINS(personDescritopn, 'FORMSOF(INFLECTIONAL, term1) AND FORMSOF(INFLECTIONAL, term2)')

Is this the best way to do this? And, if it is, how do I pass a paramatized query to that AND, is there any way of just passing the phrase into the query rather than doing indivisual FORMSOF(INFLECTIONAL, term2.. etc..

Becaseu I am building an asp.net page on top of this.. If the user searches for 'Professional Electrician South London' Do I have to split that up and pass each term individually or can I just pass the phrase and get sql server to do the work?

Thanks

 

[nice95gle]

Looks good, I can't think of another way.

Taken From BOL

This example returns all products that contain either the phrase "sasquatch ale" or "steeleye stout."

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

This example returns all product names with at least one word starting with the prefix choc in the ProductName column.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')

Well Done is better than well said
- Ben Franklin