SQL Server - Percent based Full Text Search

Question

Hi

I want to conduct search on a particular column of a table in such a way that returning result set should satify following 2 conditions:

1. Returning result set should have records whose 90% of the characters matches with the given search text.

2. Returning result set should have records whose 70% of the consecutive characters matches with the given search text.

It implies that when 10 character word Sukhminder is searched, then:

it should return records like Sukhmindes, ukhminder, Sukhmindzr, because it fulfils both of the above mentioned conditions.

But it should not return records like Sukhmixder because it does not fulfil the second condition. Likewise, It should not return record Sukhminzzz because it does not fulfil the first condition.

I am trying to use Full Text Search feature of SQL Server. But, could not formulate the required query yet. Kindly reply ASAP.

Answer 1

You could try using a combination of the SOUNDEX command and DIFFERENCE command with full text searching.

Check out this Google book online which talks about it

Answer 2

Do you mean 70% of the original word? I think the only way you could do this exactly as stated would be to work out all possible string permutations that could match the 70% criteria and bring back records matching any of those

Col LIKE '%min%' AND (
Col LIKE '%Sukhmin%' OR Col LIKE '%ukhmind%' 
OR Col LIKE '%khminde%'  OR Col LIKE '%hminder%' )

then do further processing to see if the 90% criteria is met.

Edit: Actually you might find this link on Fuzzy Searching to be of interest http://anastasiosyal.com/archive/2009/01/11/18.aspx