Fulltext search miss records in select

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henrik Juel - 19 Feb 2007 17:03 GMT

Running

SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')

or

SELECT productid,productname from productsFullText
where
contains (*, 'x2000')

gives 1 hit.

SELECT productid,productname from productsFullText
where
productname like '%x2000%'

gives 7 hits.

I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup

Signature

Regards
Henrik Juel

Reply to this Message

Hilary Cotter - 20 Feb 2007 13:09 GMT

Full-text search indexes words at word boundaries. With a like it will do a
pattern match. So full-text will return hits to x2000, whereas your like
statement will return hits to x20001, or wwwx2000.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Running
>
[quoted text clipped - 19 lines]
> ItemCount.
> Whats wrong in my setup

Reply to this Message

Henrik Juel - 20 Feb 2007 19:11 GMT

Thx for the answer.

Just realised the difference from like and freetext which from the beginning
should have been obvious for me.

Too bad that there are no possiblity for using search in between
wordsboundaries by Full-text. Maeby I go for a mix of <Like> and <Freetext>
in a union complex.

Henrik

> Full-text search indexes words at word boundaries. With a like it will do
> a pattern match. So full-text will return hits to x2000, whereas your like
[quoted text clipped - 23 lines]
>> ItemCount.
>> Whats wrong in my setup

Reply to this Message