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
You need to create a function to get the mention functionality.
create or replace
PARAM1 in varchar2
) return number as
SELECT TO_NUMBER(DECODE(SUBSTR(REPLACE(PARAM1,',',''),-1),'-', '-'||SUBSTR(REPLACE(PARAM1,',',''),1, LENGTH(REPLACE(PARAM1,',','')) -1) , REPLACE(PARAM1,',','') ))
With this you can remove ',' and if - is present in amount it will remove and keep it at the starting point otherwise no change