Search code examples
postgresqlregexp-replace

Postgresql: removing spaces between certain type of digits

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.

Solution

  • Try this query:

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