cancel
Showing results for 
Search instead for 
Did you mean: 

Remove comma

former_member557244
Participant
0 Kudos

Sir, I have this query

SELECT 10*CAST(REPLACE('PKR 5,032.25','PKR','') AS DECIMAL (10,3))  FROM DUMMY;

This shows error message

invalid number: not a valid number string ' 5,032.25' at function __typecast__() (at pos 15)

Please help me to make this query workable.

Accepted Solutions (1)

Accepted Solutions (1)

Jörg_Brandeis
Contributor

Hi Tariq,

you have to remove the comma. Either with nested REPLACE calls or with a regular expression, that removes the characters that are not allowed in a numeric string.

SELECT  10 * TO_DECIMAL(
		         REPLACE_REGEXPR(
		             '[A-Z ,]' 
		             IN 'ABC 1,122.2123' 
		             WITH '')) 
  FROM dummy;

So you can use the logic for all currencies..

Regards,
Jörg

Answers (1)

Answers (1)

former_member557244
Participant
0 Kudos

Sir this also works

SELECT 10*CAST(REPLACE(REPLACE ('PKR 5,032.25',',',''),'PKR','')AS DECIMAL (10,2)) AS "Result"  FROM DUMMY;