Postgresql converting strings with parenthesis for negative and leading $ signs-CodePudding

I have values like these in data input and I have not been able to convert those with parenthesis to a negative numeric. I am using TO_NUMBER(a.Total_Paid,'L999999D99')

Example ($123.45)

It should be -123.45

CodePudding user response：

Does this work for you? (Note that it does not work for me if I include the L in the format string)

to_number(translate('($123.45)', '()', '<>'), '999999d99PR')

CodePudding user response：

I can do it like this, but looking for something that is faster.

case when position('(' in a.Total_Paid_to_Provider)>0 then TO_NUMBER(a.Total_Paid_to_Provider,'L999999D99')* -1 Else TO_NUMBER(a.Total_Paid_to_Provider,'L999999D99') end