CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1postgresql-9.2
kimdasuncion12
kimdasuncion12

Reputation: 349

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

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.

Upvotes: 2

Views: 16216

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

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.

Upvotes: 0

Trung Duong
Trung Duong

Reputation: 3475

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

Upvotes: 0

klin
klin

Reputation: 121574

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));

Upvotes: 6

Related Questions