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 🙂