10

I have a MySql data base with a 'user.email' field. I want to update the table to remove everything before the @ symbol so I am just left with a column of email domains. Can't seem to get me query to run. Any help is appreciated!

Chris
  • 101
  • 1
  • 3
  • You want to update the data or just query it? If you just want to query it, someone else had an answer for this: http://stackoverflow.com/questions/2628138/how-to-select-domain-name-from-email-address – mark879 Nov 18 '14 at 19:03

4 Answers4

12

Keep everything after '@'

UPDATE users SET email = SUBSTR(email,LOCATE('@',email) + 1)
WHERE LOCATE('@',email) > 0;

Keep everything starting '@'

UPDATE users SET email = SUBSTR(email,LOCATE('@',email))
WHERE LOCATE('@',email) > 0;
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
2

Try this:

select right(email, charindex('@', reverse(email)) - 1)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Assuming your table name is USERS and has EMAIL as column

Before update your email column data would be like aa@xyz.com 123@syz.com

update users SET email=replace(email,left(email, INSTR(email, '@')-1),'');

After update xyz.com syz.com

If you do not need the @ symbol in the domain then remove '-1' from the query

Bharath
  • 156
  • 5
1

try this first:

select (SUBSTR(email, INSTR(email, '@') + 1)) from user

if its ok then update as:

update user set email = (SUBSTR(email, INSTR(email, '@') + 1))
Jay Ehsaniara
  • 1,421
  • 17
  • 24