substring_index is very handy.
Assume you have a name field that contains ‘Firstname Lastname’ and you want to order a SELECT statement by last name.
The simple answer is
ORDER BY substring_index(namefield, ‘ ‘, -1)
which will take the last part of the string back to the first space. A -2 in this case would get the whole string.