[MYSQL] Ordering search results using substring_index
I was recently asked to amend the ordering of the listings on a customer website. The customer wanted a list of names ordering by surname, but unfortunately, whoever designed the database had decided to store the full name in one field in the database (e.g. "Mr. Alan Partridge"). This made the sorting much more awkward than if each part of the name had been in separate fields.
After some research into MySQL's string functions, I came across substring_index , which looked like it could save me having to pull out an unsorted array and then write some PHP functionality to sort the array on surnames. Sure enough, aftersome playing with SQL queries, I was able to sort on the last word of the name field by using the following query:
The substring_index function above is taking the 'name' field, splitting it on spaces (the second argument to the function is the separator you wish to use) and returning the last token after separating (signified by -1).
back