Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Query to split my fields into two?

westmatrix's profile image westmatrix posted 13 years ago in General Permalink
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's profile image ansgar posted 13 years ago Permalink
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's profile image westmatrix posted 13 years ago Permalink
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.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.