0

I have strings representing currencies from all over the world, I've stripped the currency symbols but I'm struggling with commas in the output.

sum(CASE
           WHEN (t.value ~ '^([0-9]+.?[0-9]*|.[0-9]+)$'::text) THEN (t.value)::numeric
           ELSE (0)::numeric
           END),

I get the following error with this: 

invalid input syntax for type numeric: "750,000"

I can't make sense of the regex, everything here should parse only 0-9 and escape '.', but there's a comma leaking through and I can't see where that could happen.

woodbine
  • 553
  • 6
  • 26
  • Have you tried to debug with RegExp debugger? ex. https://regex101.com/ – Piotr Korlaga Apr 11 '20 at 08:14
  • To match a dot, it must be escaped. `.` matches any char. – Wiktor Stribiżew Apr 11 '20 at 08:19
  • If the comma is always the group separator, then just remove it: `replace(t.value, ',', '')::numeric` –  Apr 11 '20 at 08:19
  • Thanks @a_horse_with_no_name, I should have said, I'm trying to up my knowledge on regex and wanted to see if I could it without using `replace`. Thanks @Wiktor, the problem I have with regex is that I get the principles but always fall short in the implementation, I'll see how I go. – woodbine Apr 11 '20 at 08:24
  • Well, you have to get rid of the `,` before you can cast the value. There is no way around it. The only other alternative is to use `to_number()` with the correct format mask –  Apr 11 '20 at 08:35
  • I get it now, the regex is working to validate whether or not a string can be parsed, I then need to parse it, and so I do need the replace. – woodbine Apr 11 '20 at 08:39

0 Answers0