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 · Accepted 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

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

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

Examples related to sql-server

Examples related to type-conversion