CODEWITHSUNDEEP
sql-servert-sql
Melinda
Melinda

Reputation: 1531

Trying to convert NVARCHAR column value to DECIMAL(10,2)

I am getting this error when I'm trying to convert all nvarchar values in a specific column in my table and remove the $ from the value as well.

Here is the error:

Msg 8114, Level 16, State 5, Line 7
Error converting data type nvarchar to numeric.

Here is my code:

UPDATE dbo.CMS_BI_Demand 
SET DemandAmount = (SELECT CONVERT(DECIMAL(10, 2), REPLACE(DemandAmount, '$', '')) as DemandAmt
                    FROM dbo.CMS_BI_Demand
                    WHERE BISysID = 1)

Any help/direction would be appreciated.

Here is my code that corrected the error:

UPDATE dbo.CMS_BI_Demand
SET DemandAmount = t1.DemandAmount
FROM (
    SELECT BISysID, CONVERT(DECIMAL(10,2), REPLACE(REPLACE(DemandAmount,'$',''),',','')) as DemandAmount
    FROM dbo.CMS_BI_Demand  
) t1
WHERE dbo.CMS_BI_Demand.BISysID = t1.BISysID;

Upvotes: 0

Views: 610

Answers (3)

bastos.sergio
bastos.sergio

Reputation: 6764

To figure out what values your SQL SERVER instance can't properly cast to decimal(10,2), use this:

SELECT DemandAmount
FROM dbo.CMS_BI_Demand
WHERE TRY_CONVERT(DECIMAL(10,2), REPLACE(REPLACE(DemandAmount,'$',''),',','')) IS NULL

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131483

Different cultures have different decimal and thousand separators. When you call CONVERT the server will use the locale specified through the LANGUAGE setting to try and parse the string. At best, if the string and locale don't match, you'd get an error. The worst case is that the string will be passed using the wrong separator.

Given the $ symbol, it looks like you're trying to parse a US-style numeric string in a non-US locale. The solution is to use PARSE or TRY_PARSE with en-us as the culture :

select parse('234.5' as numeric(10,2) using 'en-us')

You can avoid trimming the currency sign if you parse to money first :

select parse('$234.5' as money using 'en-us')

DON'T try to replace separators. This can easily lead to wrong values and still fail to parse the text. What if the value was 2,345.123? Even if you remove the thousand separator, trying to convert this value eg in Germany would produce 2345123.00 :

select parse('2345.123' as decimal(10,2) using 'de-DE')

Returns :

2345123.00

You may assume this won't happen in production. How about the EUR/USD exchange rate? In Italy? 

SELECT PARSE('1.12' as decimal(10,2) using 'it-IT')

------
112.00

Oops

Upvotes: 0

Greg V
Greg V

Reputation: 141

Are you sure the values are all numeric after replacing the $? I ran into this once and had to do some data cleanup before I could do the update. In my case, there was a space in one of the rows so it failed. As soon as I fixed the record, everything worked.

You didn't specify what sql you're running but if you're using Microsoft SQL, I would run a check using isnumeric and verify that nothing returns as false:

SELECT id, ISNUMERIC(REPLACE(DemandAmount,'$','')) as DemandAmt
    FROM dbo.CMS_BI_Demand
    WHERE BISysID = 1 AND ISNUMERIC(REPLACE(DemandAmount,'$','')) = 0

Upvotes: 1

Related Questions