I am adding a username field to my database, and I want to populate it with the username portion of the email addresses the users have supplied...

I dont have many users, so I dont expect any conflicts.

What would a query look like to take the username portion of an email address and save it to the username column?

Thank you

  • 1
    to take the username portion of an email address SUBSTRING_INDEX(email, '@', 1) save it to the username column UPDATE. – Akina Nov 30 '18 at 12:30

I'd probably suggest doing it in the code that will be creating the insert statement but it can be done in the insert statement itself too.

INSERT INTO yourtable SET username = LOWER(SUBSTR(useremail,1,LOCATE('@',useremail) - 1))

I included the LOWER so the style of the user name would be consistent in case a person entered their email address in MixedCase.

| improve this answer | |
  • Hey Thanks a lot Dave, but shouldnt that be update yourtable set username.... – Jeffrey L. Roberts Dec 1 '18 at 4:49
  • In the absence of an actual query in your our original question which said I am adding a username I made the assumption you were inserting a row. If you are in fact updating existing rows then an update would be correct and useremail would be the name of the column that contains that value. – Dave Dec 1 '18 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.