2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

remove , and - from decimal value

Hi have an amount value in a column which is having  datatype as string.

When i am doing any addition or some other operations on that amount getting invalid number exception.

one more point is in that amount column if amount is negative then it is displaying like 123-.

I want to convert this to -123.

if the value=1,560.65- then it should display like "-1560.65"

if the value=1,560.65 then it should display like "1560.65"


Please help me how can i get this

userimage

You need to create a function to get the mention functionality.

create or replace

FUNCTION remove_spec_minus

(

  PARAM1 in varchar2 

) return number as

var2 number;

BEGIN

 

SELECT TO_NUMBER(DECODE(SUBSTR(REPLACE(PARAM1,',',''),-1),'-', '-'||SUBSTR(REPLACE(PARAM1,',',''),1, LENGTH(REPLACE(PARAM1,',','')) -1) , REPLACE(PARAM1,',','') ))

INTO VAR2

FROM DUAL;

  RETURN VAR2;

END remove_spec_minus;


With this you can remove ',' and if - is present in amount it will remove and keep it at the starting point otherwise no change

Answer is