How to find and replace string using REGEXP_REPLACE in postgresql

Question

I have a table of email addresses:

CREATE TABLE contacts(
    email     VARCHAR(255)
)

INSERT INTO contacts VALUES    
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]');

How can I find and replace the email format so [email protected] -> [email protected]?

E.g:

UPDATE contacts
SET email = REGEXP_REPLACE(email, '@', '@test.com');

Results in [email protected]

Playground here: https://dbfiddle.uk/GnIfomiO

Nick · Accepted Answer · 2022-09-17 10:35:39Z

1

This is probably most simply done by splitting the email address in two on the @, keeping the part before it and replacing . in the part after it with nothing. Then you can just append @test.com to the result:

UPDATE contacts
SET email = SPLIT_PART(email, '@', 1) || '_' || REPLACE(SPLIT_PART(email, '@', 2), '.', '') || '@test.com';

Output for your demo:

email
[email protected]
[email protected]
[email protected]

Demo on dbfiddle

answered Sep 17, 2022 at 10:35

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Comments

jian · Accepted Answer · 2022-09-17 10:41:36Z

1

demo: https://dbfiddle.uk/0KWPVeAI

UPDATE contacts
SET email = REGEXP_REPLACE(email, '@', '_gmailcom@');

UPDATE contacts
SET email = REGEXP_REPLACE(email, '@.*$', '@test.com');

The regex pattern is @ follow all the chars to end of string

answered Sep 17, 2022 at 10:41

jian

5,0131 gold badge29 silver badges46 bronze badges

1 Comment

Nick Over a year ago

What if the email address is not @gmail.com?

Collectives™ on Stack Overflow

How to find and replace string using REGEXP_REPLACE in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related