Alternatives to LIKE and FTS for mid name search I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc.

The LIKE '%%' causes table scan, however returns the exact results for names containing . The FTS keeps returning results that are "sort of same". FTS is usually offered as an alternative, but I don't understand why. Isn't this meant for unstructured data search? In my opinion it's no alternative for structured data like names.

I've also tried implementations of SOUNDEX, Levenshtein, etc., etc. But nothing gives me the desired performance.

I'm really curious how the experts handle this!



I was afraid of that already :). I have several "names", like organisationname, and indeed, firstname, surname, etc. So the thing our users sometimes only want to use parts of the name where the part to search for is in the middle of a name.

I've tried the following

SELECT  *
FROM    [dbo].[companies] a 
WHERE   a.[organisationname] LIKE '%FEK%'


This returns 60 rows, which is the preferred result. However causes a table scan because of the first % wildcard.

When using FTS

SELECT  * 
FROM    [dbo].[companies] a 
WHERE   CONTAINS(a.[organisationname], '"*FEK*"')


This returns 11 rows. It seems like the wildcard before the term is eliminated.

SELECT  * 
FROM    [dbo].[companies] a 
WHERE   CONTAINS(a.[organisationname], 'FORMSOF(INFLECTIONAL, "FEK")')


This returns 2 rows. Inflectional doesn't have any effect (probably "of course").

Hope you can give me a clue on how to better use FTS in this, or perhaps another alternative.
There are only two ways to search text data in SQL Server and you've just eliminated both of them.  I assume you have a name field which has first name and last name in it and you want to be able to search for everyone with the name John?  Can you provide some examples of what isn't working correctly?