Replace all email addresses after @ in Sql

Question

I have a list of emails. I want to change all of them to test emails for my test system e.g. [email protected] to [email protected]. I don't want to use actual emails as it will create problems. Is it possible to change all emails at once in a single query or stored procedure.

Answer 1

I tried this and it worked perfectly.

 UPDATE myTable  SET UserEMail = 
 (SELECT SUBSTRING(UserEMail, 0, PATINDEX('%@%',UserEMail)) + '@example.org' 
 from myTable U WHERE U.UserID = myTable.UserID)

Answer 2

This one works for me:

UPDATE [TABLE] [EMAIL] = REPLACE([EMAIL], '@company.com', '@test.com')