How to convert float to varchar in SQL Server

Solution 1:

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

Solution 2:

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)

Solution 3:

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

Solution 4:

Convert into an integer first and then into a string:

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