[SOLVED] SQL – Concat query, values from part of another field

Issue

SQL noob here and a bit out of my depth 😉

I have an update query that will change a Moodle account username using concat to add our organisations domain, essentially changing it to an email address:

UPDATE mdl_user SET username=CONCAT(username,'@ourdomain.ac.uk')

However, there is an issue as not all users use the same domain. I would like to modify this query to concat just the domain part from the user email field in the same mdl_user table. I guess another option would be to create a query that said make ‘username’=’email’?

Thank you,

Nige.

Solution

You can use SUBSTRING_INDEX:

UPDATE mdl_user SET username=CONCAT(username, '@', SUBSTRING_INDEX(email, '@', -1))

Answered By – sagi

Answer Checked By – Clifford M. (BugsFixing Volunteer)