CODEWITHSUNDEEP
sql-servertype-conversion
bbilal
bbilal

Reputation: 231

SQL Server: Error converting data type nvarchar to numeric

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;

Upvotes: 22

Views: 248976

Answers (4)

Bedir
Bedir

Reputation: 586

If your compatibility level is SQL Server 2012 (110) or higher, you can just use TRY_CONVERT instead of CONVERT.

SELECT
    TRY_CONVERT(DECIMAL(18, 2), COLUMNA) AS COLUMNA
FROM
    dbosu.TABLEA;

TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Upvotes: 1

Mr Yeyo
Mr Yeyo

Reputation: 11

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.

Upvotes: 1

Chandrika
Chandrika

Reputation: 207

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.

Upvotes: 18

Emad Khalil
Emad Khalil

Reputation: 823

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

Upvotes: 37

Related Questions