Avatar of hugooguh

asked on 

Mysql regular expression to split Firstname and Lastname

I have a users table and I would like to sort it using a regular expression (to split the firstname and lastname and to order it using the lastname)

Here’s the structure of the table users

id      fullname
1      "John, Smith"
2      "Paul, Brown"

Here’s what I want to achieve:

SELECT fullname FROM users ORDER BY "lastname of row fullname"
MySQL Server

Avatar of undefined
Last Comment
Terry Woods
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image


I am not sure you want/need regular expressions in this instance.  You should be able to use some simple string functions http://dev.mysql.com/doc/refman/4.1/en/string-functions.html here to achieve what you need.

SELECT fullname
FROM users
ORDER BY LEFT(fullname, INSTR(fullname, ',')-1)

If you have rows that may not have a comma, then you can wrap with IF or CASE statement to display/sort by full string value otherwise split on the comma.
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Argh, hit submit too soon.  As I neared the bottom of my post, I noticed that you have "Firstname, Lastname".  When I saw the comma, it made me think of "Lastname, Firstname".

As a consequence, LEFT(...) is not what you want, but the concept is the same.  

You could use: LTrim(RIGHT(fullname, INSTR(REVERSE(fullname, ','))-1))
Added LTrim, so you account for both ',' and ', ' the same.
Avatar of hugooguh


I get this error msg:

Incorrect parameter count in the call to native function 'REVERSE'

Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Just in case you're interested, regular expressions in MySQL can only be used to return true or false for matching or not matching, thus they cannot be used to split a string or select a portion of one.
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo