SQL Server Error converting data type nvarchar to numeric

Question

If I run the SQL query below  I get the following error       Error converting data type nvarchar to numeric    COLUMNA contains only numbers  negative and positive  including fields with maximal up to two digits after the decimal and is stored as dot decimal   IF OBJECT ID  st00 TABLEA   U   IS NOT NULL DROP TABLE  st00 TABLEA  SELECT      COLUMNA AS COLUMNA s      CASE WHEN  COLUMNA       THEN 0 ELSE CONVERT NUMERIC 18 2  REPLACE  COLUMNA            END AS COLUMNA INTO st00 TABLEA FROM dbosu TABLEA    I also tried the following  but still same problem   IF OBJECT ID  st00 TABLEA   U   IS NOT NULL DROP TABLE  st00 TABLEA  SELECT      COLUMNA AS COLUMNA s      CONVERT DECIMAL 18 2  COLUMNA  AS COLUMNA INTO st00 TABLEA FROM dbosu TABLEA

User · Answer

I was running into this error while converting from nvarchar to float.
What I had to do was to use the LEFT function on the nvarchar field.

Example: Left(Field,4)

Basically, the query will look like:

Select convert(float,left(Field,4)) from TABLE

Just ridiculous that SQL would complicate it to this extent, while with C# it's a breeze!
Hope it helps someone out there.

User · Answer

In case of float values with characters  e      it errors out if we try to convert in decimal    2 81104e 006    It still pass ISNUMERIC test   SELECT ISNUMERIC  2 81104e 006      returns 1   SELECT convert decimal 15 2    2 81104e 006      returns      error  Error converting data type varchar to numeric    And  SELECT try convert decimal 15 2    2 81104e 006      returns NULL   SELECT convert float   2 81104e 006      returns the correct value 2811040

User · Answer

You might need to revise the data in the column  but anyway you can do one of the following -  1- check if it is numeric then convert it else put another value like 0  Select COLUMNA AS COLUMNA s  CASE WHEN Isnumeric COLUMNA    1 THEN CONVERT DECIMAL 18 2  COLUMNA   ELSE 0 END AS COLUMNA   2- select only numeric values from the column  SELECT COLUMNA AS COLUMNA s  CONVERT DECIMAL 18 2  COLUMNA  AS COLUMNA where Isnumeric COLUMNA    1

[sql-server] SQL Server: Error converting data type nvarchar to numeric

Examples related to sql-server

Examples related to type-conversion