skip to Main Content

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

2

Answers


  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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search