Earlier today a client requested I change the output of their lead form to split the “full name” entry into “first name” and “last name”. My initial thought was to use a Regular Expression (RegExp) to match the parts of the string for my UPDATE query…

Whie playing around with the syntax required for my RegExp I found a much easier solution. If you are familiar with C-based languages (such as Perl, Java, PHP, etc) substr() or a similar function is in your arsenal. For some reason I did not think to look through string functions in MySQL first (*face->palm*).

Here is a real-world example of what I needed to do. If you’re here, this will likely be just what you needed to split a single field into 2 separate fields using a few simple queries.

ALTER TABLE `leads` ADD `lead_first_name` VARCHAR(64) NOT NULL, ADD `lead_last_name` VARCHAR(64) NOT NULL;
UPDATE `leads` SET `lead_first_name` = SUBSTRING_INDEX(`lead_full_name`, ' ', 1), `lead_last_name` = SUBSTRING_INDEX(`lead_full_name`, ' ', -1);
ALTER TABLE `leads` DROP `lead_full_name`;

As you will see from the example above, I used SUBSTRING_INDEX(string, delimiter, count). You may have noticed that “count” was negative to get the other half of the full name. Now keep in mind, this example depends on the original field only having a single space in all entries. We manually reviewed the table data before running this query to make sure it would not fail.

If you intend to attempt this solution, don’t forget to back up your table if you include the “ALTER TABLE” portion. Otherwise you can simply ad the 2 new fields and populate them based on the original entry using SUBSTRING_INDEX(). Enjoy 🙂