SQL update statement to replace string partially like after @ text of an email address

Question

I want a query to update a table named users where emails are not entered properly.

Like this:

praneeth@gmail
ram@gmail.co
krishna@gma
farooq@gmail.com

I need a query to make sure that all the text after @ is set to gmail.com for all the text ending with @gma or @gmail.co or @gmail or gmai

Please assist me!

BTW I'm using SQL Server 2008 Express edition.

the `UPDATE` statement syntax is fairly dependant on the RDBMS. So you can start by telling us which one you are using — Lamak, May 20 '14 at 18:51
What database are you using? And how do you wan to update the records? — Gordon Linoff, May 20 '14 at 18:51

Lamak · Answer 1 · 2014-05-20T20:44:14.087

2

UPDATE YourTable
SET Email = LEFT(Email,CHARINDEX('@',Email))+'gmail.com'
WHERE Email LIKE '%@gma'
OR Email LIKE '%@gmail.co'
OR Email LIKE '%@gmail'
OR Email LIKE '%gmai'

Here is a sqlfiddle with a demo.

And here are the results:

╔════════════════════╗
║       EMAIL        ║
╠════════════════════╣
║ praneeth@gmail.com ║
║ ram@gmail.com      ║
║ krishna@gmail.com  ║
║ farooq@gmail.com   ║
╚════════════════════╝

edited May 20 '14 at 20:44

answered May 20 '14 at 19:05

Lamak

69,480
12
108
116

Thanks for the superfast reply, I see a problem in this query. It is updating all the email to @ gmail.com. I have more mail id's like @yahoo.com ... I want them to be intact. Only Gmail mail must be changed. – PraneethArnepalli May 20 '14 at 19:22
@user3657960 Ok, I read your question the wrong way. Updated the answer now – Lamak May 20 '14 at 20:44

score 0 · Answer 2 · edited Feb 27 '15 at 19:21

0

Thanks for the replies. I got the answer here.

UPDATE TBLEmails 
SET  PersonEMail = substring(PersonEMail, 
                             1, 
                             CHARINDEX ('@', PersonEMail, 1)
                   ) + 'gmail.com' 
WHERE PersonEMail LIKE '%@gmai%'

This worked like a charm

edited Feb 27 '15 at 19:21

Rob Bednark

25,981
23
80
125

answered May 21 '14 at 14:57

PraneethArnepalli

33
2
10

SQL update statement to replace string partially like after @ text of an email address

2 Answers2