SQL Server 'like' 对浮点字段产生不一致的结果 [英] SQL server 'like' against a float field produces inconsistent results

问题描述

我正在使用 LIKE 返回与浮点字段匹配的数字结果.似乎一旦小数点左侧超过 4 位，与我在小数点右侧的搜索项匹配的值就不会返回.以下是说明这种情况的示例:

I am using LIKE to return matching numeric results against a float field. It seems that once there are more than 4 digits to the left of the decimal, values that match my search item on the right side of the decimal are not returned. Here's an example illustrating the situation:

CREATE TABLE number_like_test (
  num [FLOAT] NULL
)

INSERT INTO number_like_test (num) VALUES (1234.56)
INSERT INTO number_like_test (num) VALUES (3457.68)
INSERT INTO number_like_test (num) VALUES (13457.68)
INSERT INTO number_like_test (num) VALUES (1234.76)
INSERT INTO number_like_test (num) VALUES (23456.78)

SELECT num FROM number_like_test
WHERE num LIKE '%68%'

该查询不会返回值为 12357.68 的记录，但会返回值为 3457.68 的记录.同样使用 78 而不是 68 运行查询不会返回 23456.78 记录，但使用 76 会返回 1234.76 记录.

That query does not return the record with the value of 12357.68, but it does return the record with the value of 3457.68. Also running the query with 78 instead of 68 does not return the 23456.78 record, but using 76 returns the 1234.76 record.

所以要回答这个问题:为什么有更大的数字会导致这些结果发生变化?如何更改查询以获得预期结果?

So to get to the question: why having a larger number causes these results to change? How can I change my query to get the expected results?

推荐答案

like 运算符需要一个字符串作为左侧值.根据文档，从 float 到 varchar 的转换可以使用 几种风格:

The like operator requires a string as a left-hand value. According to the documentation, a conversion from float to varchar can use several styles:

Value         Output
0 (default)   A maximum of 6 digits. Use in scientific notation, when appropriate.
1             Always 8 digits. Always use in scientific notation.
2             Always 16 digits. Always use in scientific notation.

默认样式适用于 3457.68 中的六位数字，但不适用于 13457.68 中的七位数字.要使用 16 位而不是 6 位，您可以使用 convert 并指定样式 2.样式 2 表示一个数字，如 3.457680000000000e+003.但这对前两位数字不起作用，并且您会免费获得一个意想不到的 +003 指数.

The default style will work fine for the six digits in 3457.68, but not for the seven digits in 13457.68. To use 16 digits instead of 6, you could use convert and specify style 2. Style 2 represents a number like 3.457680000000000e+003. But that wouldn't work for the first two digits, and you get an unexpected +003 exponent for free.

最好的方法可能是从 float 转换为 decimal.该转换允许您指定比例和精度.使用标度 20 和精度 10，浮点数表示为 3457.6800000000:

The best approach is probably a conversion from float to decimal. That conversion allows you to specify the scale and precision. Using scale 20 and precision 10, the float is represented as 3457.6800000000:

where   convert(decimal(20,10), num) like '%68%'

这篇关于SQL Server 'like' 对浮点字段产生不一致的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！