MySQL / RegEXP / Split First Name and Last Name-CodePudding

Using MySQl Workbench, I am trying create a query where I take a column that has first and last name, and split it into FirstName and LastName USING REGEXP.

For example, if I have a cell from a column that has "Tom Della 'Angelo", I would like the FirstName query results to have "Tom" and the LastName query results to have "Della 'Angelo".

I have tried:

SELECT "Tom Della 'Angelo"
REGEXP ".*(?:\n|$)";

But this does not return much, other than a number.

Please do not recommend simply creating two columns, one for first and one for second. Please do not recommend something that doesn't use REGEXP.

CodePudding user response：

mysql> set @name = 'Tom Della ''Angelo';

mysql> select regexp_substr(@name, '^[[:alpha:]] ') as FirstName,
              regexp_replace(@name, '^[[:alpha:]]  ', '') as LastName;
 ----------- --------------- 
| FirstName | LastName      |
 ----------- --------------- 
| Tom       | Della 'Angelo |
 ----------- ---------------

These regexp functions require MySQL 8.0.