CODEWITHSUNDEEP
mysql
TeAmEr
TeAmEr

Reputation: 4773

strip name from email in mysql without php is it possible?

HI all ,

i have a table with column email [[email protected]] , i have 2.8 million records and i created a new column user and wish to add everything before the @ sign in the email field to the user field , is it possible with pure MySQL ?

Thank you .

Upvotes: 1

Views: 1504

Answers (4)

John Parker
John Parker

Reputation: 54445

As long as the email column consists of "standard" well formed email addresses (i.e.: not X.400, etc.), you could populate the new user field based on the existing email field as follows:

UPDATE <table name> SET user = SUBSTRING_INDEX(email, '@', 1);

See the MySQL String Functions manual page for more information.

Upvotes: 4

anubhava
anubhava

Reputation: 786329

use a select like this:

SELECT SUBSTRING_INDEX('[email protected]', '@', 1);

It returns someone.

Upvotes: 1

Adrian Serafin
Adrian Serafin

Reputation: 7725

Have a look at SUBSTRING_INDEX function. Maybe something like this will do:

SUBSTRING_INDEX(email,'@',1)

Upvotes: 0

hsz
hsz

Reputation: 152304

Try with:

UPDATE table
   SET user = SUBSTRING_INDEX(email, '@', 1)

Upvotes: 5

Related Questions