Re: Fulltext Search or Like Search

Allan wrote on Thu, 11 Jan 2007 11:11:51 +0100:

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message news:uo8ZEAyBHHA.4892@xxxxxxxxxxxxxxxxxxxxxxx
Full text for English will break words at white space. So bic black pen
would be broken as three words, bic, black and pen. Dispensers would be
broken as dispensers. In a free text search this would match with
dispensers, dispenser, dispenser's, and dispensers.

I would like to do the opposite.
When searching for "pædagog" I also want to return results that contain
"dagplejepædagog" or "pædagogmedhjælper" is ther no other way to
accomplish this than using LIKE '%pædagog%'.
Performance using LIKE is very poor, how do I improve performance?

The second example is already possible:

SELECT * FROM Table WHERE CONTAINS(*,'pædagog*')

However, the first example isn't. As Hilary has pointed out, there may be a
language dependent wordbreaker that will split the words as you require.

Another option, if you don't mind increasing the storage requirements, is to
index on a reversed version of the data too. Store a copy of the data in
another column in reverse order, such as

KEY Word RWord
1 dagplejepædagog gogadæpejelpgad

and create the FTI on both Word and RWord, then you could use:

SELECT * FROM Table WHERE CONTAINS(*,'pædagog* or gogadæp*')

However, this won't work when the term you are searching for is in the
middle of a word.

Dan


.

Loading