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.

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

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