SQL Query To Order By Surname When Name Is All One Field

Ever need to order an SQL query by the surname/last name of a user, but their full name is all one single field? Here’s how to use some of MySQL’s string function to do just that!

Here’s an example from the MySQL string functions manual page:

SELECT @string:="A horse with no name" AS String,
@loc:=LENGTH(@string) - locate(" ", reverse(@string))+1 AS
LEFT(@string, @loc), substr(@string,@loc+1);

So if we turn that into a usable example, assuming that the name field is called name and the table is users:

SELECT LEFT(`name`, (LENGTH(`name`) - locate(' ',
reverse(`name`))+1)) AS `firstname`,
substr(`name`,(LENGTH(`name`) - locate(' ', reverse(`name`))+1)+1)
AS `surname`
FROM `users`
ORDER BY `surname` ASC

Leave a Reply

Your email address will not be published. Required fields are marked *