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

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

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

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

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.


1 Answer 1


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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.