SQL Server Portal

Sometimes, you feel very handicapped when you need to convert some complex data types but due to compatibility, the built-in conversion functions such as Cast, Convert, Try_Cast, Try_Convert cannot aid conversion.

How to convert float to varchar is indeed an old topic. In this article, we will solve this issue via STR function and FORMAT function introduced in SQL Server 2012.

Given below are the two methods to convert float to varchar.

Method 1:
In this method, we will use STR function to convert float to varchar and thereafter LTRIM to trim the extra spaces left in it.

--This script is compatible with SQL Server 2005 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =LTRIM(STR(@float,50,8))
Select @varchar as [Float converted to varchar]
--OUTPUT

Method 2:
In the above method, you can notice that we used two different methods (STR, LTRIM) to convert float to varchar and to trim extra spaces. In this method we will use ONLY FORMAT function to convert float to varchar.

--This script is compatible with SQL Server 2012 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =Format(@float,'#.#############')
Select @varchar as [Float converted to varchar]
--OUTPUT