How to format and insert a character in between a string- Postgresql

Question

I have this table.

member_id | phone_number -----------+-------------- 1 | 09151234567 2 | 09904897851 3 | 09196332111 4 | 09957935412 5 | 09251279633 6 | 09357489412 7 | 09155974684 (7 rows)

I format the phone_number column to this format (63)xxxxxxxxx. Using this code
update member set phone_number = regexp_replace(phone_number, '0(.)', '(63)');

member_id | phone_number -----------+--------------- 1 | (63)151234567 2 | (63)904897851 3 | (63)196332111 4 | (63)957935412 5 | (63)251279633 6 | (63)357489412 7 | (63)155974684 (7 rows)
And now I need to update this column again to this format (63)xxx-xxxxxx. I don't want to replace a character, I need to insert a character in between the string. I've tried using substring, regexp_replace but failed. I'm doing something wrong. I did find questions somehow related to this but they are all about string replacement.

If you have that correct format, why don't you just use substring(phone_number from 1 for 7) || '-' || substring(phone_number from 8) ? — Pham X. Bach
– Pham X. Bach, Commented Mar 29, 2017 at 3:00

klin · Accepted Answer · 2017-03-29 03:04:25Z

6

You can do that in a few ways, I like this one with right() and left():

update member 
set phone_number = format('%s-%s', left(phone_number, 7), right(phone_number, -7));

answered Mar 29, 2017 at 3:04

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Trung Duong · Accepted Answer · 2017-03-29 03:17:22Z

0

To convert from format (63)xxxxxxxxx to (63)xxx-xxxxxx, you could try

UPDATE member SET phone_number = regexp_replace(phone_number, '(\(\d{2}\)\d{3})(\d{6})', '\1-\2');

You could also convert directly from original format to (63)xxx-xxxxxx by using the following

UPDATE member SET phone_number = regexp_replace(phone_number, '(0\d)(\d{3})(\d{6})', '(63)\2-\3');

I've create a sample at this link http://rextester.com/VCX4636

edited Mar 29, 2017 at 3:17

answered Mar 29, 2017 at 3:10

Trung Duong

3,4752 gold badges10 silver badges10 bronze badges

Comments

Tomáš Záluský · Accepted Answer · 2019-02-02 22:45:19Z

0

You can also utilize lookbehinds.

select regexp_replace('(63)xxxxxxxxx','(?<=.{7})','-')

This is particularly useful when there is complex expression instead of phone_number and hence it is not desirable for readability to use it repetitively in multiple calls of left(), right() or substring() function.

answered Feb 2, 2019 at 22:45

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Collectives™ on Stack Overflow

How to format and insert a character in between a string- Postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related