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
Advertisement
Answer
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