How to Convert Float to Varchar in Sql Server

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; }
}

.