Converting Float to Varchar in SQL Server, In Non Scientific Form

December 27, 2013 4:43 pm
Andri Kasta Marengga

Converting Float to Varchar in SQL Server, In Non Scientific Form

Recently I got a problem about my data in SQL Server database. Here’s the chronology :

  1. I have a data on DB (let’s says this is Table A) that should be updated based on an Excel file from a user
  2. Because the Excel file has a huge number of rows, I decide to import this file to DB first (let’s name this Table B) then update my data based on this imported data
  3. I can’t join data in Table A to Table B because its column has a different data type -___-
    After some checking, i realized that Table A’s column is varchar, and Table B’s is float

The problem is converting float to varchar isn’t as simple as I think. Simple conversion using SQL Server built-in function “CONVERT” will result a scientific form like 1123e+888 and of course I still unable to join my tables.

Here’s my simple research to solve this problem …


DECLARE @myFloat FLOAT = 1234567890;

SELECT CONVERT(VARCHAR(20),@myFloat)
-- This will produce '1.23457e+009'

SELECT STR(@myFloat, 20)
-- This will produce ' 1234567890' (with extra space)

SELECT LTRIM(STR(@myFloat, 20))
-- This will produce '1234567890'

Hope that helps 🙂

No comments yet.

Leave a Reply