MySQL Sorting results

When we have a table lets call it “users” and we have several colums like: “ID”, “UserFullName”, “Address”….  and we want to select all the rows BUT we want to sort the result by THE LAST NAME of the user… Oh issue there because we do not have a column called “LastName” we have the full name in one column like this: “UserFullName” => “Jean Michel Jarre” <— the last name should be “Jarre” so one of the first toughts here would be….

create a SELECT statement in MySQL to fetch all rows and then with php clean the LasName

end(explode(‘ ‘, $fullName))

Store everything in an array and then use “asort” , but what If we want to do all of this in just one simple SQL statement…

So here is the SQL Query to use:

SELECT ID, UserFullName, Address FROM users ORDER BY SUBSTRING_INDEX(UserFullName,’ ‘,-1);

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

So:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

Leave a Reply

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