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.
