Find exact match using full-text search

Question

Using the Sql Server 2008 how can you actually find an exact string match using full-text search. I'm having a real hard time with this and I just couldn't find a satisfactory solution anywhere online.

For example, if I'm searching for the string "Bojan Skrchevski" I want the first result to be exactly that.

So far I've tried formatting the string like: "Bojan* NEAR Skrchevski*" and call CONTAINSTABLE to get results, but this string is formatted to return more results as Bojana and Bojananana etc. I also tried to ORDER BY RANK, but still no success.

Furthermore, in my string I have a number sequence like: "3 1 7", but with the current formatting it also returns "7 1 3" etc.

Example:

DECLARE @var varchar(4000);
SET @var = '"Oxford*" NEAR 24 NEAR 7 NEAR 5 NEAR "London*"'
SELECT [Key] FROM CONTAINSTABLE(dbo.[MyTable], [MyField], @var);

I want to be able to get the exact ordering. Not to get "Oxford 7 24 5 London" as a result.

How do I format the string to accomplish this correctly?

Answer 1

There's 2 options

1) This will get all items which have Mountain in their name

SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO

2) This will get all items which have these 3 strings in Document no matter what order

SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components' );

It depends on what you really want but I couldn't understand completely.

If I'm missing the point please post a sample and what the result should be.

kr,

Kristof