Project to update domain and userid part of email address.

We have one table with subscribers email address in one column, we have another two columns domain & userid with NULL data. We have to develop a single query to update these two columns by domain and userid part of the email address.

The SQL dump of the table ( plus2net_newsletter ) is available at end of this tutorial. Use that to create table with four records. There are four records with sample data and all have NULL data for userid and domain columns.

Using string functions we will separate domain and uesrid part from email address and then update the respective columns in a MySQL table.

For better understanding of the final query, we will test first with small quires and check the outputs.

Use LOCATE function to find position of the @ symbol of the email address.
SELECT email, LOCATE('@',email) FROM `plus2net_newsletter`
emailLOCATE('@',email)
userid_1@example1.com 9
userid_2two@example2.co.in 12
userid3@example3.uk 8
userid44@example4.net 9
Use LEFT function to get the left part of the email address from the position of @
SELECT email, LEFT(email,LOCATE('@',email)) FROM `plus2net_newsletter`
email LEFT(email,LOCATE('@',email))
userid_1@example1.com userid_1@
userid_2two@example2.co.in userid_2two@
userid3@example3.uk userid3@
userid44@example4.net userid44@
We need to reduce this by 1 to exclude @ from the result .
SELECT email, LEFT(email,LOCATE('@',email)-1) FROM `plus2net_newsletter`
email LEFT(email,LOCATE('@',email))
userid_1@example1.com userid_1
userid_2two@example2.co.in userid_2two
userid3@example3.uk userid3
userid44@example4.net userid44
We will use SUBSTRING function to get the domain part
SELECT email, SUBSTRING( email, LOCATE(  '@', email )+1 ) as domain FROM  `plus2net_newsletter`
We have added +1 to the Query to remove @ in the output
Output is here
email domain
userid_1@example1.com example1.com
userid_2two@example2.co.in example2.co.in
userid3@example3.uk example3.uk
userid44@example4.net example4.net
Now let us display both userid and domain part separately.
SELECT email, SUBSTRING( email, LOCATE(  '@', email )+1 ) as domain, LEFT(email,LOCATE('@',email)-1) as userid FROM  `plus2net_newsletter`
Using the above data let us create the query to Update the domain and userid columns of each record.
email domain userid
userid_1@example1.com example1.com userid_1
userid_2two@example2.co.in example2.co.in userid_2two
userid3@example3.uk example3.uk userid3
userid44@example4.net example4.net userid44
Now using above quires we will update our table with domain part and userid part.
UPDATE plus2net_newsletter SET userid=LEFT(email,LOCATE('@',email)-1) , domain=SUBSTRING( email, LOCATE(  '@', email )+1 )
Now both columns are updated with domain and userid part of the email address, the final table is here.
email domain userid
userid_1@example1.com example1.com userid_1
userid_2two@example2.co.in example2.co.in userid_2two
userid3@example3.uk example3.uk userid3
userid44@example4.net example4.net userid44

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Your Rating


Google+

Post Comment This is for short comments only. Use the forum for more discussions.




Subscribe to our mailing list

* indicates required
Subscribe to plus2net




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2017 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer