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;

Emad Khalil · Accepted Answer · 2015-10-21 09:27:07Z

38

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

edited Oct 21, 2015 at 9:27

answered Oct 21, 2015 at 8:55

Emad Khalil

8331 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bbilal Over a year ago

Thanks for your input; it gives me another error now however: An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.

bbilal Over a year ago

Thanks! Now it works. So in your query you check whether the cells are numeric; if yes; then convert them. What I don't understand though; COLUMNA actually has only numeric values in the table (both negative and posit numbers, as well as zeros). Do you know why the check for isnumeric still is necessary?

Emad Khalil Over a year ago

you are welcome.. data needs to be investigated somehow.. you can use the output from this query and compare it with the original data in the table, if you found difference in a value, that value might be the problem

Jindřich Matouš 3 hours ago

it is not reliable, please see: techcommunity.microsoft.com/blog/sqlserver/…

zx485 · Accepted Answer · 2018-10-18 20:30:31Z

19

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.

edited Oct 18, 2018 at 20:30

zx485

29.1k28 gold badges55 silver badges65 bronze badges

answered Oct 18, 2018 at 20:01

Chandrika

2172 silver badges4 bronze badges

1 Comment

edhubbell Over a year ago

This saved me in an XML parsing situation where scientific notation appears once in a while - Many thanks. SELECT ISNULL(DataRows.ID.value(N'(@SomeValThatFails)[1]','DECIMAL(6,5)'), -1), ISNULL(DataRows.ID.value(N'(@SomeOtherValThatWorks)[1]','FLOAT'), -1) FROM @XML.nodes('./XMLNodes') AS DataRows(ID)

groenhen · Accepted Answer · 2020-06-23 14:55:16Z

1

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.

edited Jun 23, 2020 at 14:55

groenhen

3,02725 gold badges51 silver badges69 bronze badges

answered Jun 22, 2020 at 23:14

Mr Yeyo

111 bronze badge

1 Comment

karlis ssl Over a year ago

Thanks, i tried everything i could think off, nothing worked, i guess there is something else in the field. However i would recommend going higher than left(Field,4), i used left(Field,48) as i have defined the column as nvarchar(50)

Bedir · Accepted Answer · 2023-06-29 19:49:44Z

1

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.

edited Jun 29, 2023 at 19:49

answered Jun 29, 2023 at 17:05

Bedir

5861 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL Server: Error converting data type nvarchar to numeric

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related