Mysql regular expression to split Firstname and Lastname

hugooguh
hugooguh used Ask the Experts™
on
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"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Hi.

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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.

Author

Commented:
I get this error msg:

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

Thanks
Chief Technology Officer
Most Valuable Expert 2011
Commented:
LTrim(RIGHT(fullname, INSTR(REVERSE(fullname), ',')-1))
Terry WoodsIT Guru
Most Valuable Expert 2011

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial