Sql Server 2008 - FullText四舍五入的价值？ [英] Sql Server 2008 - FullText rounding money values?

问题描述

让我们假设我们有一个带有这些记录的全文索引表：

  blabla bla bla 101010,65 blabla bla bla 
 blabla bla bla 1012344,34 blabla bla bla 
  

（葡萄牙语中的小数点分隔符是， 不，如英文）

当我们执行一个查询时：

  where contains（field，101011）or 
 where contains（field，1012344）
  

全文引擎正在返回这些记录，因为在我看来它正在将数字四舍五入为：

  101010,65变成101011 
 1012344,34变成1012344 
  

有没有什么办法避免这样做？

编辑

对不起，我忘了说列是varchar max列而不是货币列。尽管它是一个varchar列，但它有一个浮点值，但在这个字段中发生了这个事实。

EDIT2

这不是我列中唯一的数据。这些数字经常出现在我编入索引的文本中。它不是连接的。正如我所说，这是原文的一部分，我对原文没有做任何事。我猜这是断字符的行为，但是谁肯定知道？

解决方案

编辑：

<忽略>
您看到此行为的原因是，SQL全文搜索的默认单词分隔符是由英语语言（区域设置1033）定义的。在英语中，逗号是一个有效的断字符，从而将您的号码分成两个不同的号码。但是，如果您使用葡萄牙文的分词器，FTS很巧妙地将数字保留在一起。尝试在SQL Server上运行以下查询，以查看全文引擎如何根据指定的区域设置以不同方式解析相同的输入：

  --use locale English 
 select * from sys.dm_fts_parser（''12345,10''，1033，NULL，0）
 --use locale Portuguese 
 select * from sys.dm_fts_parser（ '12345,10'，2070，NULL，0）
  

< / Ignore>

UPDATE：
好​​的，我设法复制了你的场景，并且它似乎是SQL Server FTS的默认行为。然而，它似乎只是最接近该数字的十分之一（在你的案例中最接近的10个分位数），而不是最接近的整数。

所以例如; 12345,88将返回搜索 12345,88以及12345,9 ，而56789,98将出现在搜索56789,98以及56790.然而，一个数字，如45678,60将保持完整，没有向上或向下的四舍五入，所以它没有你想象的那么糟糕。

不确定是否有任何事情可以改变这种行为虽然。 Google上的快速搜索没有任何结果。

Lets assume we have a full text indexed table with those records:

blabla bla bla 101010,65 blabla bla bla 
blabla bla bla 1012344,34 blabla bla bla 

(The decimal separator in Portuguese is "," not "." as in English)

When we execute a query like:

where contains(field, "101011") or
where contains(field, "1012344")

The full text engine is returning those records because it seems to me that it is rounding the numbers as:

101010,65 becomes 101011
1012344,34 becomes 1012344

Is there any way of avoiding that?

EDIT

Sorry, i forgot to say that the column is a varchar max column and not a currency column. This is happening in this field when it has a float value despite the fact that it is a varchar column

EDIT2

This is not the only data I have in my column. Numbers like those appears frequently on my indexed texts. It is not concatenated. As I said, this is part of the original text and I have done nothing to the original text. I guess this is a behavior of the word breaker, but who knows for sure?

解决方案

EDIT:

< Ignore > The reason you are seeing this behaviour is that, the default wordbreakers for SQL fulltext search are defined by the English language (locale 1033). In English, a comma is a valid word-breaker, thereby breaking your number into two different numbers. However, if you use the Portuguese word-breaker, FTS quite cleverly retains the numbers together. Try running the following query on your SQL Server to see how the fulltext engine parses the same input differently depending on the locale specified:

--use locale English
select * from sys.dm_fts_parser('"12345,10"',1033,NULL,0)
--use locale Portuguese
select * from sys.dm_fts_parser('"12345,10"',2070,NULL,0)

< /Ignore >

UPDATE: Alright, I have managed to replicate your scenario and yes it does seem to be default behaviour with SQL Server FTS. However, it only seems to round up to nearest 1/10th of the number (the nearest 10 centavos in your case), and NOT to the nearest whole number.

So for example; 12345,88 would be returned in searches for both 12345,88 as well as 12345,9, while 56789,98 would appear in searches for 56789,98 as well as 56790. However, a number such as 45678,60 will remain intact with no rounding up or down, so it's not as bad as you think.

Not sure if there is anything you can do to change this behaviour though. A quick search on Google returned nothing.

这篇关于Sql Server 2008 - FullText四舍五入的价值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！