3

I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string.

    if OBJECT_Id('tempdb..#whydis') is not null begin drop table #whydis end
    if OBJECT_Id('tempdb..#ImSeriously') is not null begin drop table #ImSeriously end

    create table #whydis (bigID float)
    create table #ImSeriously (bigID varchar(255))

    insert into #whydis(BigID)
    values(1495591),
    (1495289),
    (1495610),
    (1495611),
    (1495609),
    (1495592),
    (1495686)

    INSERT INTO #ImSeriously (bigID)
    SELECT BigID from #whydis

    select * from #ImSeriously

results look like this:

1.49559e+006

Scientific notation stored as a string. It's easy enough to work around by casting as int:

    INSERT INTO #ImSeriously (bigID)
    SELECT cast(BigID as int) from #whydis

But the whole thing has me scratching my head.

Question: What is it about floats that stores them this way?

0

2 Answers 2

8

This is documented under CAST and CONVERT:

For a float or real expression, style can have one of the values shown in the following table. 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.

Applies to: SQL Server (Starting in SQL Server 2016 (13.x)) and Azure SQL Database.
126, 128, 129 Included for legacy reasons; a future release could deprecate these values.

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

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 might like to use STR or FORMAT instead:

DECLARE @f float;

SET @f = 123456789012345e294;

SELECT 
    LTRIM(STR(@f, 309, 0)),
    FORMAT(@f, 'F0');

Both produce the output:

12345678901234500000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000

For float, integers from −253 to 253 (−9,007,199,254,740,992 to 9,007,199,254,740,992) can be exactly represented. For real, integers between −16777216 and 16777216 can be exactly represented.

3

The thing to remember about float as a datatype is that the values are just an estimate of a number. It's not going to be an exact number.

As far as why SQL Server displays them in scientific notion? Because it is representing estimates, a float contains a truncated value that only has significance to a certain number of digits. This may be a throw-back to a lesson on "significant digits" in a science course you took in school. The notation used is expressly to help define the precision of the values being represented. Trailing zeros may or may not be part of that precision.

The docs are really helpful:

Values of float are truncated when they are converted to any integer type.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

If having the values retain accuracy and never be truncated, you should not use float.

You can see the different ways that different functions can be used to format floating-point numbers in this example, as well as seeing the truncation by using a larger (and smaller) number:

DECLARE @float float = 123456789012345678901234567890;
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);

SELECT  StrWith0Decimals    = STR(@float,30,0),
        StrWith1Decimals    = STR(@float,30,1),
        StrWith4Decimals    = STR(@float,30,4);

SELECT  FormatWorksToo      = FORMAT(@float,'####'),
        FormatWorksToo2     = FORMAT(@float,'####.##');


SET @float = 12.3456789012345678901234567890;
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);

SELECT  StrWith0Decimals    = STR(@float,30,0),
        StrWith1Decimals    = STR(@float,30,1),
        StrWith4Decimals    = STR(@float,30,4);

SELECT  FormatWorksToo      = FORMAT(@float,'####'),
        FormatWorksToo2     = FORMAT(@float,'####.##');
1
  • 3
    "the values are just an estimate of a number" - no no no no. They aren't mystical quantum uncertainty clouds! Floating point quantities are exact numbers - just, perhaps, not the values you asked for...
    – AakashM
    Jun 29, 2021 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.