How to convert float to varchar in SQL Server

Question

I have a float column with numbers of different length and I m trying to convert them to varchar   Some values exceed bigint max size  so I can t do something like this  cast cast float field as bigint  as varchar 100     I ve tried using decimal  but numbers aren t of the same size  so this doesn t help too  CONVERT varchar 100   Cast float field as decimal 38  0      Any help is appreciated   UPDATE   Sample value is 2 2000012095022E 26

User · Answer

This can help without rounding  declare  test float 25   declare  test1 decimal 10 5   select  test   34 0387597207 select  test set  test1   convert  decimal 10 5    test  select cast   test1  as varchar 12     Select  LEFT cast   test1  as varchar 12   LEN cast   test1  as varchar 12   -1

User · Answer

Try using the STR   function   SELECT STR float field  25  5    STR   Function    Another note  this pads on the left with spaces  If this is a problem combine with LTRIM   SELECT LTRIM STR float field  25  5

User · Answer

I just came across a similar situation and was surprised at the rounding issues of  very large numbers  presented within SSMS v17 9 1   SQL 2017   I am not suggesting I have a solution  however I have observed that FORMAT presents a number which appears correct  I can not imply this reduces further rounding issues or is useful within a complicated mathematical function   T SQL Code supplied which should clearly demonstrate my observations while enabling others to test their code and ideas should the need arise     WITH Units AS       SELECT 1 0 AS  RaisedPower     Ten  As UnitDescription    UNION ALL    SELECT 2 0 AS  RaisedPower     Hundred  As UnitDescription    UNION ALL    SELECT 3 0 AS  RaisedPower     Thousand  As UnitDescription    UNION ALL    SELECT 6 0 AS  RaisedPower     Million  As UnitDescription    UNION ALL    SELECT 9 0 AS  RaisedPower     Billion  As UnitDescription    UNION ALL    SELECT 12 0 AS  RaisedPower     Trillion  As UnitDescription    UNION ALL    SELECT 15 0 AS  RaisedPower     Quadrillion  As UnitDescription    UNION ALL    SELECT 18 0 AS  RaisedPower     Quintillion  As UnitDescription    UNION ALL    SELECT 21 0 AS  RaisedPower     Sextillion  As UnitDescription    UNION ALL    SELECT 24 0 AS  RaisedPower     Septillion  As UnitDescription    UNION ALL    SELECT 27 0 AS  RaisedPower     Octillion  As UnitDescription    UNION ALL    SELECT 30 0 AS  RaisedPower     Nonillion  As UnitDescription    UNION ALL    SELECT 33 0  AS  RaisedPower     Decillion  As UnitDescription     SELECT UnitDescription                    POWER  CAST 10 0 AS FLOAT 53      RaisedPower                                                                AS ReturnsFloat             CAST  POWER  CAST 10 0 AS FLOAT 53      RaisedPower     AS NUMERIC  38 0                                           AS RoundingIssues      STR    CAST  POWER  CAST 10 0 AS FLOAT 53      RaisedPower     AS NUMERIC  38 0        CAST  RaisedPower  AS INT    2  0  AS LessRoundingIssues      FORMAT       POWER  CAST 10 0 AS FLOAT 53      RaisedPower        0                                                       AS NicelyFormatted  FROM Units ORDER BY  RaisedPower

User · Answer

this is the solution I ended up using in sqlserver 2012  since all the other suggestions had the drawback of truncating fractional part or some other drawback    declare  float float   1000000000 1234  select format  float  N                                       output   1000000000 1234   this has the further advantage  in my case  to make thousands separator and localization easy   select format  float  N     0               de-DE      output   1 000 000 000 1234

User · Answer

Select cast replace convert decimal 15 2  acs daily debit             as varchar 20    from acs balance details

User · Answer

float only has a max  precision of 15 digits  Digits after the 15th position are therefore random  and conversion to bigint  max  19 digits  or decimal does not help you

User · Answer

select replace myFloat            from REPLACE   documentation      Returns nvarchar if one of the input arguments is of the nvarchar data type  otherwise  REPLACE returns varchar    Returns NULL if any one of the arguments is NULL    tests  null      NULL  1 11     1 11 1 10     1 1 1 00     1 0 00     0 -1 10     -1 1 0 00001     1e-005 0 000011     1 1e-005

User · Answer

If you use a CLR function  you can convert the float to a string that looks just like the float  without all the extra 0 s at the end   CLR Function   Microsoft SqlServer Server SqlFunction DataAccess   DataAccessKind Read    return  SqlFacet MaxSize   50   public static SqlString float to str double Value  int TruncAfter      string rtn1   Value ToString  R      string rtn2   Value ToString  0     new string  0   TruncAfter       if  rtn1 Length  lt  rtn2 Length    return rtn1    else   return rtn2           Example  create table  temp  value float  insert into  temp values  0 73    0    0 63921    -0 70945    0 28    0 72000002861023    3 7    -0 01    0 86    0 55489    0 439999997615814   select value         dbo float to str value  18  as converted         case when value   cast dbo float to str value  18  as float  then 1 else 0 end as same from    temp  drop table  temp      Output  value                  converted                  same ---------------------- -------------------------- ----------- 0 73                   0 73                       1 0                      0                          1 0 63921                0 63921                    1 -0 70945               -0 70945                   1 0 28                   0 28                       1 0 72000002861023       0 72000002861023           1 3 7                    3 7                        1 -0 01                  -0 01                      1 0 86                   0 86                       1 0 55489                0 55489                    1 0 439999997615814      0 439999997615814          1      Caveat  All converted strings are truncated at 18 decimal places  and there are no trailing zeros   18 digits of precision is not a problem for us   And  100  of our FP numbers  close to 100 000 values  look identical as string values as they do in the database as FP numbers

User · Answer

Convert into an integer first and then into a string   cast  convert int b tax id   as varchar 20

User · Answer

Useful topic thanks   If you want like me remove leadings zero you can use that    DECLARE  MyFloat  float   SET  MyFloat   1000109360 050  SELECT REPLACE RTRIM REPLACE REPLACE RTRIM LTRIM REPLACE STR  MyFloat  38  16    0                 0

User · Answer

Try this one  should work   cast  convert bigint b tax id   as varchar 20

User · Answer

The only query bit I found that returns the EXACT same original number is   CONVERT  VARCHAR 50   float field 128    See http   www connectsql com 2011 04 normal-0-microsoftinternetexplorer4 html  The other solutions above will sometimes round or add digits at the end  UPDATE  As per comments below and what I can see in https   msdn microsoft com en-us library ms187928 aspx   CONVERT  VARCHAR 50   float field 3    Should be used in new SQL Server versions  Azure SQL Database  and starting in SQL Server 2016 RC3

User · Answer

Modified Axel s response a bit as it for certain cases will produce undesirable results   DECLARE  MyFloat  float   SET  MyFloat   1000109360 050   SELECT REPLACE RTRIM REPLACE REPLACE RTRIM  REPLACE CAST CAST  MyFloat AS DECIMAL 38 18   AS VARCHAR max     0                 0

User · Answer

SELECT LTRIM STR float field  25  0     is the best way so you do not add  0000 and any digit at the end of the value

User · Answer

Based on molecular s answer   DECLARE  F FLOAT   1000000000 1234  SELECT  F AS Original  CAST FORMAT  F  N                                    AS VARCHAR  AS Formatted   SET  F   823399066925 049 SELECT  F AS Original  CAST  F AS VARCHAR  AS Formatted UNION ALL SELECT  F AS Original  CONVERT VARCHAR 128    F  128  AS Formatted UNION ALL SELECT  F AS Original  CAST FORMAT  F  N G   AS VARCHAR  AS Formatted   SET  F   0 502184537571209 SELECT  F AS Original  CAST  F AS VARCHAR  AS Formatted UNION ALL SELECT  F AS Original  CONVERT VARCHAR 128    F  128  AS Formatted UNION ALL SELECT  F AS Original  CAST FORMAT  F  N G   AS VARCHAR  AS Formatted

[sql-server-2008] How to convert float to varchar in SQL Server

Examples related to sql-server-2008