Error: Error Converting data type nvarchar to Numeric

Question

Error: Error Converting data type nvarchar to Numeric

Asked 6 years, 8 months ago

Viewed 3,477 times

0

I am trying to convert data from a column in NVARCHAR(255) to DECIMAL(10,2), however, all the ways I tried always gives the same error Error Converting data type nvarchar to Numeric. I created another column of type DECIMAL(10,2) to transfer the data from the column of type NVARCHAR(255), but even with CONVERT or CAST also does not convert. Follow the code below:


    SELECT CASE 
             WHEN Isnumeric(salario)  0 THEN CONVERT(NVARCHAR(255), 
                                             CONVERT(DECIMAL(10, 2), salario)) 
             ELSE salario 
           END AS [SALÁRIO] 
    FROM dbo.rendimentos

1

How is the format of this string in the database? maybe it is in an invalid format to convert to decimal. Something else. may be giving error because the column is null or empty string.

– Rodrigo K.B

2018/03/12 at 21:09
The salary column is set to allow Nulls values, but has no null value. Using the ISNUMERIC() function, all the data in this column return the value 1, that is, all are numerical. Even so, it returns the conversion error.

– Kelly Soares

2018/03/13 at 16:08
@Kellysoares What is the decimal separator: semicolon?

– José Diz

2018/03/13 at 22:54

1 answer

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.

by José Diz • **6,942** points · Answer 1 · 2018-03-13T22:48:01+00:00

Evaluate the following suggestion:

-- código #1  v2
SELECT salario,
       convert(decimal(10, 2), salario) as [SALÁRIO] 
  from dbo.rendimentos
  where Isnumeric(salario) = 1;

However, if the column values salario use the comma as decimal separator, the error occurs. If this is the case, the correct code is:

 -- código #2 v3
 SELECT salario,
        convert(decimal(10, 2), replace(salario, ',', '.')) as [SALÁRIO] 
  from dbo.rendimentos
  where Isnumeric(salario) = 1;

Demo: http://sqlfiddle.com/#! 18/3f02a/1