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 lastoccurrence, 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 |