Postgresql: inserting spaces between certain type of digits

Question

Related to my previous posting Postgresql: removing spaces between certain type of digits, I want to do the opposite, namely to insert spaces between postal codes. I might have a column with an address such as '01031970 São Paulo SP, BR'.

I want to insert a space between the fourth and fifth numeric digit, i.e. '0103 1970 São Paulo SP, BR'.

Any ideas how to do it with regexp_replace?

klin · Accepted Answer · 2017-09-22 16:10:34Z

1

With regexp:

select regexp_replace(str, '(^.{4})(.*)', '\1 \2')
from (
    values('01031970 Sao Paulo SP, BR')
    ) v(str);

       regexp_replace       
----------------------------
 0103 1970 Sao Paulo SP, BR
(1 row)

Without regexp (may be a bit faster for larger data):

select concat(left(str, 4), ' ', right(str, -4))
from (
    values('01031970 Sao Paulo SP, BR')
    ) v(str);

           concat           
----------------------------
 0103 1970 Sao Paulo SP, BR
(1 row)

answered Sep 22, 2017 at 16:10

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: inserting spaces between certain type of digits

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related