CODEWITHSUNDEEP
sql-servert-sql
Abdul
Abdul

Reputation: 2120

Replace all email addresses after @ in Sql

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.

Upvotes: 16

Views: 39650

Answers (8)

Ricardo
Ricardo

Reputation: 11

All data is potentially valuable. If you need to ensure emails can NEVER be sent out from a test system, I suggest ... SET email = 'XXX' + email + 'XXX'.

Upvotes: -1

Orlando Reynoso
Orlando Reynoso

Reputation: 71

This one works for me:

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

Upvotes: 5

HS07
HS07

Reputation: 23

UPDATE employees SET email=REPLACE(email, SUBSTR(email,INSTR(email,'@')+1),'test.com');

Upvotes: 2

Abdul
Abdul

Reputation: 2120

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)

Upvotes: 25

Prashant
Prashant

Reputation: 5461

Try this

UPDATE users SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1),
'example.com')

Upvotes: 17

Abhishek
Abhishek

Reputation: 2490

Try the below code snippet - 

create table #email (emailid varchar(20));
    insert into #email
    select '[email protected]' union
    select '[email protected]' union
    select '[email protected]'
    select * from #email;
    select replace(emailid,left(substring(emailid,charindex('@',emailid)+1,len(emailid)),charindex('.',substring(emailid,charindex('@',emailid)+1,len(emailid)))-1),'test' from #email;

Upvotes: 1

Deep Kalra
Deep Kalra

Reputation: 1428

Add a column in the table structure and define it as: CONCAT(SUBSTRING([old_column],0,CHARINDEX('@',[old_column])),'@test.com') AS [new_column]

Refer to M.Ali's answer here to add a new column based on old column: Alter a Table by adding a new column whose value depends on another column

Use + and ISNULL instead of CONCAT in case you are using a sql-server version older than SQL Server 2012

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93734

Use STUFF or LEFT string functions. Try this

DECLARE @emails VARCHAR(200) = '[email protected]'

SELECT Stuff(@emails, Charindex('@', @emails), Len(@emails), '') + '@test.com'

SELECT LEFT(@emails, Charindex('@', @emails)-1) + '@test.com'

Upvotes: 1

Related Questions