-1

SQL Server : i want to convert my varchar datatype to float but i do have some scientific notation values in it(for ex. 3.11955748130297E-02) and i am getting following error : Error converting data type varchar to float.

I have tried it using convert and cast functions, but that's not working.

insert into table1--(data type as float) 

select convert(float,column1),-- contains '3.11955748130297E-02' as value and datatype is varchar 

convert(float,column2) 

from table2
Code Different
  • 90,614
  • 16
  • 144
  • 163
user1989
  • 11
  • 1
  • 5
  • 1
    Possible duplicate of [SQL Server 2005 : Converting varchar value '1.23E-4' to decimal fails](http://stackoverflow.com/questions/2148990/sql-server-2005-converting-varchar-value-1-23e-4-to-decimal-fails) – juergen d Jun 27 '16 at 18:39
  • 1
    `SELECT CONVERT(float, '3.11955748130297E-02')` works for me on my SQL Server 2012 server. Can you provide the non-working code? – Code Different Jun 27 '16 at 18:42
  • insert into table1--(data type as float) select convert(float,column1),-- contains '3.11955748130297E-02' as value and datatype is varchar convert(float,column2) from table2 – user1989 Jun 27 '16 at 18:53
  • @CodeDifferent : i have pasted the sample of the code – user1989 Jun 27 '16 at 19:05
  • Are you sure `3.11...E-02` was the one that caused troubles? How about searching for the ones that do: `SELECT column1, column2 FROM table2 WHERE TRY_CONVERT(float, column1) IS NULL OR TRY_CONVERT(float, column2) IS NULL` – Code Different Jun 27 '16 at 19:31

1 Answers1

1

the error was coming due to the 'column break' in one of the columns , i tried it using the below code:

convert(float,REPLACE(column1, CHAR(13), '')) column1

thanks a lot for the responses.

user1989
  • 11
  • 1
  • 5