Data Manipulation via SQL

jnuneznyc

I have inherited an intranet which was developed in Perl and MySQL. The user table structure is a bit strange and I would like to clean it up and then normalize it.

The username and department resides in one field separated by @ symbol.

Sample:

USERID USERNAME ...
     1  John Doe@Accounting ...
     2  Jane Doe@Account Services

Is there an UPDATE SQL query that will allow me to Retrieve it.. Separate it and then update without using PHP?

daynah

...Without using PHP or some kind of script language? I don't think there mysql has an advance function like that.

What I would do is this.

Create another field called "dept."

Write a script that goes through the database, gets the "username" field which is:

John Doe@Accounting

$mydata = explode('@', $username_field);

$mydata[0] will have the value 'John Doe'
$mydata[1] will have the value 'Accounting'

From here, you can update username = $mydata[0] and dept = $mydata[1]

Do the same for the entire database. To be on the safe side, I would create another field called 'username2' and update everything in that. When everything is done, then I can delete the original username field.

jnuneznyc

I GOT IT!!!

This query will copy the right side of the @ symbol to department

UPDATE `users` SET `department` = SUBSTRING( `username`, LOCATE( '@', `username` )+1 )

This query will copy the left side of the @ symbol to user_name [UNTESTED]

UPDATE `users` SET `user_name` = SUBSTRING( `username`, 1, LOCATE( '@', `username` )-1 )

NOTE: I created user_name in this table since I can't take it offline and content is constantly changing.