August 29, 2006 at 8:46 am
Hi,
I just implemented Full-Text search on my website and things are working great with the exception of one thing. For example, if I search for "drink 100ml" and the actual description is "drink 100ml bottle", I get all results that contain drink AND 100ml, which I want. If I search for "drink 300", where the actual description is "drink 300 ml bottle", no results are returned. the "300" is ignored. The SQL version is 2000, the column indexed is varchar. Here's the basic query. I have looked at CONTAINSTABLE and FREETEXTTABLE, but I keep reading the the search condition for both cases cannot be a variable. I was able to use FREETEXT and return the "drink 300 ml bottle" description, however, the lack of ranking was a problem. Any ideas?
SELECT * FROM TABLE1
WHERE CONTAINS(DESRIPCOLUMN,@DESCRIP_PARAMETER)
Any direction would be greatly appreciated.
August 30, 2006 at 8:49 am
Hi Pat,
First of all, you can use a vairable (your @DESCRIP_PARAMETER) with both CONTAINSTABLE and FREETEXTTABLE. However, with FREETEXT or FREETEXTTABLE the words in the variable are treated as a "bag of words" and Boolean conditions are ignored.
When you search for "drink 300" with CONTAINS or "drink 300 ml bottle" with FREETEXT is the expected results "drink 300 ml bottle" all in one column? Also, can you reply back with the full output of "select @@version" as with SQL Server 2000 both the Service pack and OS platform info is important.
Thanks,
John
SQL Full Text Search Blog
Blog: http://jtkane.spaces.live.com/
John T. Kane
August 30, 2006 at 9:07 am
Hi John,
Thank you for the reply. Yes the expected search results are stored in one column.
Here is the information you requested.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on WindowsNT 5.0 (Build 2195: Service Pack 4)
Thanks again!!
Pat
August 30, 2006 at 12:48 pm
Hi John,
I tried using a FREETEXTTABLE and this is working quite well. The ranking is fairly accurate and the search by description functionality is far more dynamic than the CONTAINS I was using previously. Thanks for the tip.
Pat
August 31, 2006 at 9:02 am
I figured out what the issue was! My original explanation was a little off, my apologies for that. I thought that all standalone numbers, such as "300", were being ignored. This is NOT the case. Only SINGLE digit numbers were being ignored, such as "3". After reading an article by Jon Winer, "Full-Text Indexing Tips", I realized the noise.enu file conatined either words or single number/letters that were ignored when the catalog was created and indexed. After removing these numbers/letters from the noise.enu file, everything is working great.
Pat
August 31, 2006 at 9:25 am
No problem, SQL FTS is somewhat of a "black box" for diagnosing these types of issues. Yes, I was somewhat suprised by the initial "300" problem in finding that text, but the problem with finding single number or single letters is well known. I'm glad you got everything sorted out!
Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/
John T. Kane
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply