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

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

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

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

Examples related to sql-server

Examples related to type-conversion