SQL server 'like' against a float field pr

问题:

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%'

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?

回答1:

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.

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.

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%'

回答2:

When you are comparing number with LIKE it is implicitly converted to string and then matched
The problem here is that float number is not precise and when it is converted you can get
13457.679999999999999 instead of 13457.68

So to avid this explicitly format number in appropriate format(not sure how to do this in sql server, but it will be something like)

SELECT num FROM number_like_test
WHERE Format("0.##",num) LIKE '%68%'

回答3:

The conversion to string is rounding your values. Both CONVERT and CAST have the same behavior.

SELECT cast(num as nvarchar(50)) as s
FROM number_like_test

Or

SELECT convert(nvarchar(50), num) as s
FROM number_like_test

provide the results:

1234.56
3457.68
13457.7
1234.76
23456.8

You'll have to use the STR function and correct format parameters to try to get your results. For example,

SELECT STR(num, 10, 2) as s
FROM number_like_test

gives:

   1234.56
   3457.68
  13457.68
   1234.76
  23456.78

回答4:

Pretty well solved already, but you only need to CAST once, not twice like the other answer suggests, LIKE takes care of the string conversion:

SELECT *
FROM number_like_test
WHERE CAST(num AS DECIMAL(12,6)) LIKE '%68%'

And here's a SQL Fiddle showing the rounding behavior: SQL Fiddle

回答5:

It's probably because a FLOAT data type represents a floating point number which is an approximation of the number and should not be relied on for exact comparisons.

If you need to do a search that includes the float value you would need to either store it in a decimal data type (which will hold the exact number) or convert it to a varchar using something like the STR() function