Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Don't know much about it myself but stack exchange ditched SQL Server FTS and now use Lucene instead –  Martin Smith Jan 12 '12 at 9:30
    
@Peter - I assume this was intended as an edit so I've merged it into the question - hope that's right. –  Jack Douglas Jan 12 '12 at 13:57

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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