Full text search alternative without leading wildcard limitation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

google@macrotex.net - 24 Mar 2006 15:32 GMT

As has been mentioned several times in this group, SQL Server does not
support a leading wildcard in full text searches (i.e., CONTAINS(field,
'*tion')).

To get around this I have been looking at external full text search
engines that might not have this restriction but with little success:
most of the others seem to have the same restriction (e.g., Lucene,
swish-e, etc.). The one that did not was Namazu but it was slow.

So my question is: has anyone found or come up with a full text search
application that does not have this wildcard limitation and is fast
(faster than simply using LIKE)?

Reply to this Message

Daniel Crichton - 24 Mar 2006 17:24 GMT

google@macrotex.net wrote on 24 Mar 2006 06:32:06 -0800:

> As has been mentioned several times in this group, SQL Server does not
> support a leading wildcard in full text searches (i.e., CONTAINS(field,
[quoted text clipped - 8 lines]
> application that does not have this wildcard limitation and is fast
> (faster than simply using LIKE)?

You could, if you have the space in your database, create a copy of the
columns you wish to search in this way in reverse, and use FTS to index
this. eg. add a column called fieldreverse, and do

UPDATE table SET fieldreverse = REVERSE(field)

then index that, and search it using

CONTAINS(field, 'noit*'))

You could automatic the creation of the reversed data using a trigger on the
table for the normal column. Not a pretty solution, but it'll work.

Dan

Reply to this Message