How to find and replace string using REGEXP_REPLACE in postgresql

alias51 0
Viewed 26

I have a table of email addresses:

CREATE TABLE contacts(
    email     VARCHAR(255)
)

INSERT INTO contacts VALUES    
    ('example.person@gmail.com'),
    ('example.person2@gmail.com'),
    ('example.person3@gmail.com');

How can I find and replace the email format so example.person@gmail.com -> example.person_gmailcom@test.com?

E.g:

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

Results in example.person@test.comgmail.com

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

regexp-replace sql postgresql regex
2 Answers

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
example.person_gmailcom@test.com
example.person2_gmailcom@test.com
example.person3_gmailcom@test.com

Demo on dbfiddle

usergAKD0

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

userBct310
Related