My new favourite SQL function – SUBSTRING_INDEX
So I needed a quick way to convert a list of authors from “Joe A. Bloggs” into “Bloggs, Joe A.” – i.e, not separated out into firstname and lastname fields, just one field called ‘name’. I then needed another field to return a single column with the first letter of the lastname for display purposes.
Turns out this magically easy in mySQL.
SUBSTRING_INDEX “returns the substring from the given string before a specified number of occurrences of a delimiter.” So in our example, “Joe A. Bloggs”, I want to return the last string delimited by a space, chuck in a comma, then put the first part of the name after. The reason I want to do the last part of the string (and not the first) is that as a generalisation, names could be in the format “firstname lastname”, “firstname initial. lastname”, or “firstname, initial1. initial2. doublebarrel-lastname” (in my particular circumstance anyway).
The resultant query then ends up looking like this:
SELECT authors.id, authors.name, CONCAT(SUBSTRING_INDEX(TRIM(authors.name),' ',-1), ', ', SUBSTRING_INDEX(TRIM(authors.name),' ', 1)) AS reversedName, LEFT(SUBSTRING_INDEX(TRIM(authors.name),' ',-1), 1) AS firstLetter FROM authors GROUP BY authors.id ORDER BY SUBSTRING_INDEX(TRIM(authors.name),' ',-1) ASC;
Neat!