Inter-word searching

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jst1699 - 31 Jul 2007 10:02 GMT

Hi,

Can you guys give me some tips on how to implement inter-word searching in
SS 2005 FTS or any non-FTS way (but has to be MS technology based - company
rules)

E.g. word - AstraZeneca
- doing FTS searching for Zeneca returns zero rows
- Astra* finds the row (OK)

I posted on
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1817332&SiteID=1

but thought I'd try my luck on the FTS forum : )

any help is much appreciated
jt

Reply to this Message

Daniel Crichton - 31 Jul 2007 12:31 GMT

jst1699 wrote on Tue, 31 Jul 2007 02:02:03 -0700:

> Hi,
>
[quoted text clipped - 13 lines]
> any help is much appreciated
> jt

SQL Server FTS only allows searching on the start of words, not the end or
middle. You can use the LIKE clause to get around this in SQL Server,eg.

SELECT * FROM MyTable WHERE MyCol LIKE '%Zeneca'

but this is often a lot slower than using FTS.

You could create a reversed FTS indexed column and search on that though,
eg.

PK: REC1
MyCol1: AstraZeneca
MyCol2: aceneZartsA

SELECT * FROM MyTable WHERE CONTAINS(*,'"aceneZ*"')

Of course this means that you have to parse the search terms and create
reversed text as necessary.

The only other thing I can suggest is that if the breaking of "words" in
your searches is on the capitalisation is that you store the broken version,
eg. Astra Zeneca, in your table and FTS that. You could do this in a second
column if necessary so that the original data is retained untouched. This
way searching on just Zeneca will return the row, and doesn't rely on using
the wildcard.

Dan

Reply to this Message