Fulltext search miss records in select

Fulltext search miss records in select

Post by Henrik Jue » Wed, 21 Feb 2007 02:03:09


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




--
Regards
Henrik Juel
 
 
 

Fulltext search miss records in select

Post by Hilary Cot » Wed, 21 Feb 2007 22:09:53

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.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.yqcomputer.com/

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

 
 
 

Fulltext search miss records in select

Post by Henrik Jue » Thu, 22 Feb 2007 04:11:28

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

"Hilary Cotter" < XXXX@XXXXX.COM > skrev i en meddelelse