Postgresql replace comma from right in position 3 and replace dot and comma left to that position

I am having a currency value with comma and dot. I need to change some European style currency to UK style value.

11,200.00 -> 11200.00
11.200,00 -> 11200.00
58.20 -> 58.20
58,20 -> 58.20
2500 -> 2500

I have tried using right() and replace functions but no luck.

Could anyone please help me in writing replace function to handle this.

Answer

This is an issue in European countries, especially when new and legacy software have to co-exist. If “European style currency values coming in” from an external source then you have no other choice but handle them. First try to determine which style the string value belongs to with regexp matching and then do the relevant correction. Something like this illustration:

select case 
 when :strval ~ '^[+-]?(d+.)*d+(,d+)$' then replace(replace(:strval, '.', ''), ',', '.')
 when :strval ~ '^[+-]?(d+,)*d+(.d+)$' then replace(:strval, ',', '')
 when :strval ~ '^[+-]?d+$' then :strval
 -- maybe there are other formats so the list may grow
end; 

As a function:

create or replace function unify_numeric_format(strval text)
returns numeric language sql immutable as
$$
select case 
 when strval ~ '^[+-]?(d+.)*d+(,d+)$' then replace(replace(strval, '.', ''), ',', '.')
 when strval ~ '^[+-]?(d+,)*d+(.d+)$' then replace(strval, ',', '')
 when strval ~ '^[+-]?d+$' then strval
 -- maybe there are other formats so the list may grow
end::numeric; 
$$;