Project to update domain and userid part of email address.

Email Userid & Domain part 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) 9 12 8 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@ userid_2two@ userid3@ 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 userid_2two userid3 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
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 userid_2two userid3 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 userid_2two userid3 userid44
SQL dump of table plus2net_newsletter
SQL String References substring_index to get part of string using delimiter
Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer