CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 111

Trying to remove everything before an '@' symbol in MySql Email column

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!

Upvotes: 11

Views: 8623

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

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;

Upvotes: 14

Bharath
Bharath

Reputation: 166

Assuming your table name is USERS and has EMAIL as column

Before update your email column data would be like [email protected] [email protected]

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

Upvotes: 2

Jay Ehsaniara
Jay Ehsaniara

Reputation: 1529

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))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Try this:

select right(email, charindex('@', reverse(email)) - 1)

Upvotes: 2

Related Questions