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`
email
LOCATE('@',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`