June 1, 2010 at 12:48 am
Hello,
I have a table with products. In this table there is a column with some information about the product, like product name, number, etc..
For example:
CR1AC1EM1SP10535PG5 008498 31755 008498 4015080317555 My product
The bold part is the number that I want to search for using the ContainsTable keyword. My query look like this:
SELECT top 10 *
FROM MyTable
INNER JOIN containstable(MyTable, ColumnProductInfo, '"4015080317555"', 1000) FTS
ON MyTable.ColumnUniqueKey = FTS.
When I run this query it doesn't only return the product with number 4015080317555, but it returns all products of which the product number start with 401508 (the first 6 digits).
The fulltext column has the Neutral language. When I change the language to English and rebuild the index, it returns only product 4015080317555 which is correct. This is what I would expect with the Neutral language as well.
I don't like to use the English language since the product names are in Dutch, but both the Neutral as the Dutch language don't work correctly.
When I change the query so that I put an * in place of the last 5 in the product number, then I do get the correct result:
SELECT top 10 *
FROM MyTable
INNER JOIN containstable(MyTable, ColumnProductInfo, '"401508031755*"', 1000) FTS
ON MyTable.ColumnUniqueKey = FTS.
I prefer do use the Dutch (or Neutral) language, because I don't know what other problems I could run into if I change to English (the database already runs at customers).
What is going wrong and what can I do to make this query work for the Dutch or Neutral language?
Any help is greatly appreciated.
~Jasper
June 1, 2010 at 7:50 am
As you probably suspect, it is down to the different rules applied for word-breaking and stemming depending on the language selected.
The new Neutral word-breaker happens to search for numbers in two forms: full decimal and scientific.
Run the following to see what I mean:
-- Neutral
SELECT *
FROM sys.dm_fts_parser(N'"4015080317555"', 0, 0, 0);
-- U.S. English
SELECT *
FROM sys.dm_fts_parser(N'"4015080317555"', 1033, 0, 0);
Notice that neutral matches on 4015080317555 and nn4d01508e+012.
sys.dm_fts_parser is a very powerful tool for debugging this sort of thing.
Paul
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply