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"
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"
Last Comment
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.
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.
ASKER
I get this error msg:
Incorrect parameter count in the call to native function 'REVERSE'
Thanks
Incorrect parameter count in the call to native function 'REVERSE'
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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 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.
49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.