Error converting data type varchar to numeric

Question

Post reply

Error converting data type varchar to numeric

Minnu

SSCertifiable

Points: 6451
More actions
June 17, 2015 at 9:17 am

#318305

Team
As per my requirment using below script to convert from varchar to numeric.
declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)
getting error : Error converting data type varchar to numeric
Please help..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The problem is the commas in the string. Try this select statement:

select CONVERT(NUMERIC(17,2),replace(@stg,',',''))

Y.B. SSChampion Points: 11649 More actions · Answer 2

I believe this was introduced in SQL 2012 feature but I thought it was interesting.

However, as it is somewhat related I thought I'd mention it. I recently discovered this little nifty function.

declare @stg varchar(50)

set @stg='12,000,00'

SELECT TRY_PARSE(@stg AS NUMERIC)

Also returns a NULL if the conversion fails which could be a good or bad thing depending on the situation.

SELECT quote FROM brain WHERE original = 1
0 rows returned

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Lynn Pettis (6/17/2015)
The problem is the commas in the string. Try this select statement:
select CONVERT(NUMERIC(17,2),replace(@stg,',',''))

Yes, it will get rid of the commas, but it seems odd that a comma would appear just 2 places to the left of where the decimal point is usually located, as European numbering often uses a comma instead of a decimal point, so your CONVERT will deliver a value of 1,200,000, where a value of 12,000 might be expected. There's no way for us to know without the original poster chiming in on this...

If it is indeed a decimal point type of comma, we might need something a little more nuanced:

DECLARE @stg AS varchar(50) = '12,000,00';

SELECT CONVERT(numeric(17,2), REPLACE(LEFT(@stg, LEN(@stg) - 3), ',', '') + REPLACE(RIGHT(@stg, 3), ',', '.'))

Minnu, what say you?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sgmunson (6/18/2015)
Lynn Pettis (6/17/2015)
The problem is the commas in the string. Try this select statement:
select CONVERT(NUMERIC(17,2),replace(@stg,',',''))
Yes, it will get rid of the commas, but it seems odd that a comma would appear just 2 places to the left of where the decimal point is usually located, as European numbering often uses a comma instead of a decimal point, so your CONVERT will deliver a value of 1,200,000, where a value of 12,000 might be expected. There's no way for us to know without the original poster chiming in on this...
If it is indeed a decimal point type of comma, we might need something a little more nuanced:
DECLARE @stg AS varchar(50) = '12,000,00';
SELECT CONVERT(numeric(17,2), REPLACE(LEFT(@stg, LEN(@stg) - 3), ',', '') + REPLACE(RIGHT(@stg, 3), ',', '.'))
Minnu, what say you?

If the comma is a decimal point, shouldn't the other comma then be a '.'?

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Lynn Pettis (6/18/2015)
sgmunson (6/18/2015)
Lynn Pettis (6/17/2015)
The problem is the commas in the string. Try this select statement:
select CONVERT(NUMERIC(17,2),replace(@stg,',',''))
Yes, it will get rid of the commas, but it seems odd that a comma would appear just 2 places to the left of where the decimal point is usually located, as European numbering often uses a comma instead of a decimal point, so your CONVERT will deliver a value of 1,200,000, where a value of 12,000 might be expected. There's no way for us to know without the original poster chiming in on this...
If it is indeed a decimal point type of comma, we might need something a little more nuanced:
DECLARE @stg AS varchar(50) = '12,000,00';
SELECT CONVERT(numeric(17,2), REPLACE(LEFT(@stg, LEN(@stg) - 3), ',', '') + REPLACE(RIGHT(@stg, 3), ',', '.'))
Minnu, what say you?
If the comma is a decimal point, shouldn't the other comma then be a '.'?

Not necessarily.. but your point is valid... That's why I asked the original poster to respond.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Minnu (6/17/2015)
Team
As per my requirment using below script to convert from varchar to numeric.
declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)
getting error : Error converting data type varchar to numeric
Please help..

If the answer is supposed to be 12000.00, then the following works.

DECLARE @stg MONEY;

SET @stg = '12,000,00';

SELECT CONVERT(NUMERIC(17,2),@stg/100.0);

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)