distal-attribute
distal-attribute
distal-attribute
distal-attribute

Query to split my fields into two?

westmatrix posted 6 years ago in General
Is there a query available to split my one field(column) into two?
One table => tbl_login

usr_id
usr_username
usr_password
usr_email //currently holds email@example.com


What I would like is

usr_id
usr_username
usr_password
usr_email // would now hold email
usr_domain // would now hold example.com


Is this possible with MySQL / HeidiSQL ?
The @ symbol could be utilized in some way to split the data, not sure.
Cheers
ansgar posted 6 years ago
Easy.
1. Add the two new fields so you have usr_email, usr_email_name, usr_email_domain or whatever
2. Run such an update query:

UPDATE tbl_login SET
usr_email_name = SUBSTRING(usr_email, 1, POSITION('@' IN usr_email)-1),
usr_email_domain = SUBSTRING(usr_email, POSITION('@' IN usr_email)+1)

westmatrix posted 6 years ago

UPDATE tbl_login SET
usr_email = SUBSTRING(usr_email, 1, POSITION('@' IN usr_email)-1),
usr_domain = SUBSTRING(usr_email, POSITION('@' IN usr_email)+1)


This updates the different columns with the same data, everything before the @

Fixed like this: one then... the other...

UPDATE tbl_login SET
usr_domain = SUBSTRING(usr_email, POSITION('@' IN usr_email)+1)

UPDATE tbl_login SET
usr_email = SUBSTRING(usr_email, 1, POSITION('@' IN usr_email)-1)



Thank you.

Please login to leave a reply, or register at first.