Postgresql converting strings with parenthesis for negative and leading $ signs

Question

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

Mike Organek · Accepted Answer · 2022-05-12 18:06:54Z

1

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')

answered May 12, 2022 at 18:06

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ken Over a year ago

Without the L it stills removes the $ sign

Mike Organek Over a year ago

@Ken Technically, it is ignoring the number sign when parsing the value into a numeric.

Collectives™ on Stack Overflow

Postgresql converting strings with parenthesis for negative and leading $ signs

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related