sql server full text search: I have come across a small problem when searching using
the freetext catalogue on a products table we have.
When doing a search for 1750TN (which is a valid part of
a product description) the record is not found, however,
if I type FS-1750TN the search finds the requested
product.

Any ideas on why the system would not find 1750TN as part
of a product code???

Iain,
What version of SQL Server (7.0 or 2000) and on what OS Platform (NT4.0,
Window 2000, Windows XP or Windows Server 2003) is it installed on? Could
you post the full output of: SELECT @@version

The above version info and especially the OS platform is very important in
determining the problem and therefore the solution to your question.
Thanks,
John



[quoted text, click to view]

John, Thanks for the reply. Here is the output from @@version:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

Iain


*** Sent via Developersdex http://www.developersdex.com ***

John,

Thanks for the reply. Here is the output from @@version:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)



*** Sent via Developersdex http://www.developersdex.com ***

You're welcome, Iain,
You're using SQL Server 2000 SP3 EE on Windows 2000 SP3.
The key to your question/issue is the OS platform-specific wordbreaker -
Win2K's infosoft.dll - and how it "breaks" words with punctuation
characters, such as a dash or hyphen "-" embedded in the "word" or in your
case the product description. Specifically, you are not able to search on
"1750TN" because the actual word in your FT-enabled column is "FS-1750TN".
Correct? However, when you search for "FS-1750TN", i.e., with the dash or
hyphen "-", the SQL FTS query finds the row.

Windows 2000's wordbreaker considers the dash or hyphen "-" since it's
embedded in the "search word", to be part of the search word since the dash
or hyphen "-" is touching or in contact with the "S" and "1" of your search
word. Depending upon what is your exact SQL FTS query (contains or freetext
?), you could use a wildcard "*" to find this on Win2K, for example:

select * from some_table where contains(*,'"FS*"')
-- note, this will return more rows than just those with "FS-1750TN"

Additionally, you may want to test your SQL FTS query with SQL Server 2000
on Windows Server 2003 or Windows XP (using the Developers Edition) as those
OS platforms use a newer wordbreaker langwrbk.dll and you will see different
results on these OS platforms depending upon the punctuation characters and
their placement in your search words.

Regards,
John







[quoted text, click to view]