PDA

View Full Version : Query to split my fields into two?


student101
07-23-2008, 05:17 PM
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 isusr_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

davidj
07-23-2008, 05:27 PM
$domain = explode("@",$row['usr_email']);

echo $domain[1];

student101
07-23-2008, 05:38 PM
Will this work in my MYSQL app to actually split the data into the different fields(columns)?

davidj
07-23-2008, 05:39 PM
you have to extract it in php and do an insert

student101
07-23-2008, 05:43 PM
Using a query like this:

$domain = explode("@",$row['usr_email']);
select * from usr_email where "$domain[1]"
insert $domain[1] into usr_email
insert $domain[2] into usr_domain

davidj
07-23-2008, 05:53 PM
$domain = explode("@",$row['usr_email']);


explode creates an array into $domain so in the above example you get the following...


$domain[0] = "email";

$domain[1] = "example.com";


so if your doing an insert with the original data then thats wher you would do your second one

student101
07-23-2008, 06:21 PM
This updates the different columns with the same data, everything before the @
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)
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)
Used standard SQL query inside HEIDIsql, same will work in others.

Thank you.