CODEWITHSUNDEEP
mysqlregexmysql-workbench
Tony Correia
Tony Correia

Reputation: 29

MySQL / RegEXP / Split First Name and Last Name

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.

Upvotes: 1

Views: 969

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 1

Related Questions