Why does SQL float casting to string not work with LIKE but SQL float casting to decimal to string d-CodePudding

I have an object with an number of type float, and I was curious why the following SQL statement does not work, but the one below it does.

SELECT TOP (1000) [number]
FROM [object]
WHERE CONVERT(varchar(255), number) LIKE ' 1608147%'

Results in "0 rows found".

SELECT TOP (1000) [number]
FROM [object]
WHERE CONVERT(varchar(255), CONVERT(decimal(20, 2), number)) LIKE ' 1608147%'

Results in 1 row found

Edit: I was asked to execute the following:

SELECT
    number, 
    CONVERT(varchar(255), number), 
    CONVERT(varchar(255), CONVERT(decimal(20, 2), number))  
FROM [object]

This yielded the following result:

number : 201608147  
number cast to string: 201608147  
number cast to decimal: 201608147.00

To show it is really a float:

CodePudding user response：

After simulation I found the following:

select nbr, Convert(varchar(255), nbr), Convert(varchar(255), Convert(decimal(20,2), nbr))  from tbl_XYZ

201608147   2.01608e 008    201608147.00

The Convert(varchar(255), nbr) returns the scientific notation of the number at hand as a string value; consequently the value does not match your pattern :

LIKE ' 1608147%'

The reason behind this behavior is that the Float DataType is used to hold the binary (base-2) approximation of a number and not a precise decimal value.

Floating point numbers are often shown in scientific notation. These types are used when range is more important than absolute precision. The numbers quickly become unwieldy in other formats. Scientific notation also helps to emphasise the limited precision. You can see the different ways that different functions can be used to format floating-point numbers in this example.

DECLARE @float float = 201608147;
SELECT TheNumber = @float;

SELECT  ConvertWithoutStyle = CONVERT(varchar(255),@float),
        ConvertWithStyle0   = CONVERT(varchar(255),@float,0),
        ConvertWithStyle1   = CONVERT(varchar(255),@float,1),
        ConvertWithStyle2   = CONVERT(varchar(255),@float,2),
        ConvertWithStyle3   = CONVERT(varchar(255),@float,3);

For a float, style can have one of the values shown below. Other values are processed as 0.

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.
3   Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string.

You are using an implicit conversion from float to varchar(255), which implicitly uses style 0. Your float has more than six digits, so it is represented in scientific notation.

You might like to use STR or FORMAT instead.