Recently I got a problem about my data in SQL Server database. Here’s the chronology :
- 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
- 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
- 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 🙂