6/21/2007
Data Manipulation - Splitting Surname and Name
For a new client project I was provided with database of users consisting of names, email addresses and managers.
CREATE TABLE `users` (
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`manager` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL
)
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`manager` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL
)
The name column contained both first and last names formatted last name first like:
lastname, fistname
Parker, Damon
Parker, Damon
MySQL’s http://dev.mysql.com/doc/refman/5.0/en/string-functions.html” rel=”external”>TRIM() functions allow us to make quick work of this data manipulation. Using the query below and a new table similar to the first except with separate fields for first and last name we can copy the data to the new table and split the names in the process.
INSERT INTO users_clean (first_name, last_name, email, manager, STATUS) SELECT TRIM(SUBSTRING_INDEX( `name` , ‘,’, 1 )) AS first_name, TRIM(SUBSTRING_INDEX( SUBSTRING_INDEX( `name` , ‘,’, 2 ) , ‘,’, -1 )) AS last_name, email, STATUS
FROM `users`
FROM `users`
Popularity: 14%
