CODEWITHSUNDEEP
sqlpostgresqlreplace
Solomon Raja
Solomon Raja

Reputation: 1800

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.

Upvotes: 1

Views: 1308

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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; 
$$;

Upvotes: 3

S-Man
S-Man

Reputation: 23686

To solve this specific problem, I would make two replace() steps:

demo:db<>fiddle

SELECT replace(replace('11.200,00', '.', ''), ',', '.')

Nonetheless, you should take the comment of @a_horse_with_no_name serious: Don't store values as a type text.

Edit: Wait, you have both variants: 11.200,00 AND 11,200.00 ? You have to normalize this table. Please switch the column type to numeric.

Upvotes: 1

Related Questions