Postgresql: removing spaces between certain type of digits

Question

I have a column with address such as '01031 970 São Paulo SP, BR'.

I want to remove spaces between postal codes. The postal code can appear anywhere in the address, e.g. 'São Paulo 01031 970 SP, BR'. The result should be 'São Paulo 01031970 SP, BR' or '01031970 São Paulo SP, BR'

regexp_replace(address, ,'(\s*[0-9]{5}\s+[0-9]{3}\s+)','(\s*[0-9]{5}[0-9]{3}\s+)', 'g')

obviously does not work, but I am looking for an equivalent that does the job.

Answer 1

Try this query:

update your_table
set address = regexp_replace(address, '([0-9]{5})\s+([0-9]{3})', '\1\2', 'g')