Float to Varchar

  • Naidu PK

    SSCrazy

    Points: 2444

    Comments posted to this topic are about the item Float to Varchar

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • jan.dewettinck

    Default port

    Points: 1465

    Thanks for the question

    Not sure if the following link provides a better explanation.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    I based my answer on the part about the style which says that the default is 6 digits.

    -------------------------------------------------------------------------------------------------------------------------------

    When expression is float or real, style can be 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.

    126, 128, 129

    Included for legacy reasons and might be deprecated in a future release.

  • patricklambin

    SSCrazy Eights

    Points: 9964

    jan.dewettinck (8/9/2015)


    Thanks for the question

    Not sure if the following link provides a better explanation.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    I based my answer on the part about the style which says that the default is 6 digits.

    -------------------------------------------------------------------------------------------------------------------------------

    When expression is float or real, style can be 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.

    126, 128, 129

    Included for legacy reasons and might be deprecated in a future release.

    +1 and thanks for this short but full and easy explanation.

    As I knew that the default number of digits was 6 , the answer to find was too easy.

    But thanks to the Creator of this QOT as it is good to post this kind of question.

  • Ed Wagner

    SSC Guru

    Points: 286958

  • Eirikur Eiriksson

    SSC Guru

    Points: 182360

    jan.dewettinck (8/9/2015)


    Thanks for the question

    Not sure if the following link provides a better explanation.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    I based my answer on the part about the style which says that the default is 6 digits.

    -------------------------------------------------------------------------------------------------------------------------------

    When expression is float or real, style can be 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.

    126, 128, 129

    Included for legacy reasons and might be deprecated in a future release.

    +1

    😎

    Default style (0) will return 6 digits.

  • SqlMel

    SSCrazy

    Points: 2891

    I based my answer on the part about the style which says that the default is 6 digits.

    I believe this is the actual reason why we get that result. Can verify by changing a bit the excercise:

    declare @f float=115627.473121036902

    select convert(varchar(20), @f)

    Result: 115627

    ---------------
    Mel. 😎

  • Rich Mechaber

    SSChampion

    Points: 10935

    Yeah, well, be careful using STR() to convert from FLOAT to VARCHAR(), too:

    http://www.sqlservercentral.com/questions/T-SQL/94788/[/url]

    Rich

  • Naidu PK

    SSCrazy

    Points: 2444

    jan.dewettinck (8/9/2015)


    Thanks for the question

    Not sure if the following link provides a better explanation.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    I based my answer on the part about the style which says that the default is 6 digits.

    -------------------------------------------------------------------------------------------------------------------------------

    When expression is float or real, style can be 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.

    126, 128, 129

    Included for legacy reasons and might be deprecated in a future release.

    Good to know this stuff. Thanks Jan

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • Naidu PK

    SSCrazy

    Points: 2444

    rmechaber (8/10/2015)


    Yeah, well, be careful using STR() to convert from FLOAT to VARCHAR(), too:

    http://www.sqlservercentral.com/questions/T-SQL/94788/[/url]

    Rich

    Good example on STR() usage.

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • Revenant

    SSC-Forever

    Points: 42467

    Very nice - thanks, Naveen!

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • webrunner

    One Orange Chip

    Points: 29948

    Great question - and even more counter-intuitive than the answers usually are!

    Thanks.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice one, good to know, thanks.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply