Archive for category: mySQL

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!