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

Query to split my fields into two?

User, date Message
westmatrix Written by westmatrix
6 years ago
Category: General
82 posts since Wed, 11 Apr 07
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 Written by ansgar
6 years ago
5098 posts since Fri, 07 Apr 06
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 Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07

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.