How to convert float to varchar in SQL Server

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.

15 Answers

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)) 

The only query bit I found that returns the EXACT same original number is

CONVERT (VARCHAR(50), float_field,128) 

See

The other solutions above will sometimes round or add digits at the end

UPDATE: As per comments below and what I can see in :

CONVERT (VARCHAR(50), float_field,3) 

Should be used in new SQL Server versions (Azure SQL Database, and starting in SQL Server 2016 RC3)

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 

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.

Convert into an integer first and then into a string:

cast((convert(int,b.tax_id)) as varchar(20)) 

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'),'.',' ')),' ',',') 

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.

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) 

Try this one, should work:

cast((convert(bigint,b.tax_id)) as varchar(20)) 

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 < 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.

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'),'.',' ')),' ','.') 

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

Select
cast(replace(convert(decimal(15,2),acs_daily_debit), '.', ',') as varchar(20))

from acs_balance_details

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; 

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]