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.

Tim Biegeleisen · Accepted Answer · 2017-09-15 08:41:05Z

1

Try this query:

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

edited Sep 15, 2017 at 8:41

answered Sep 15, 2017 at 8:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

Can you elaborate on "doesn't work?" I think my regex is correct.

Florian Seliger Over a year ago

sorry, my mistake. Yes, it indeed works if you leave out the \s* at the beginning and the \s+ at the end (as in your regex). Thank you for your help!

Collectives™ on Stack Overflow

Postgresql: removing spaces between certain type of digits

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related