SQL Server: Error converting data type nvarchar to numeric

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.

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

Related