CODEWITHSUNDEEP
sqlsql-server-2008full-text-search
TehBoyan
TehBoyan

Reputation: 6890

Find exact match using full-text search

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?

Upvotes: 6

Views: 7264

Answers (2)

TehBoyan
TehBoyan

Reputation: 6890

Perhaps one approach could be to select several results with the full-text search and then SELECT the specific one from those results. But maybe there could be a better solution to this.

I tried this approach and it actually worked. It also works a lot faster then to just SELECT the value.

Upvotes: 0

Kristof Heiremans
Kristof Heiremans

Reputation: 11

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

Upvotes: 1

Related Questions