I'm working with MySQL via WordPress and I would like to sort the title based on its words being in reverse order. For example:
Samuel Leroy Jackson -> Jackson Leroy Samuel
Samuel L Jackson -> Jackson L Samuel
Samuel Jackson -> Jackson Samuel
Samuel L Jackson Jr -> Jr Jackson L Samuel
I would like to do this via MySQL so that I can sort based on the results.
It's not that SQL databases are inefficient at text processing, but since SQL tends to treat a column as an atomic value, the operations to work on substrings and other parts of values aren't very convenient.
For example, in MySQL, it's kind of awkward to try to split a string into an array. There really isn't an array data type.
The closest thing is SUBSTRING_INDEX(). You can get a substring based on a character separator. In this case, use a space as the separator.
mysql> SET @name = 'Samuel Leroy Jackson';
mysql> SELECT SUBSTRING_INDEX(@name, ' ', -1) AS `last name`,
-> SUBSTRING_INDEX(@name, ' ', 1) AS `first name`,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(@name, ' ', 2), ' ', -1) AS `middle name`;
+-----------+------------+-------------+
| last name | first name | middle name |
+-----------+------------+-------------+
| Jackson | Samuel | Leroy |
+-----------+------------+-------------+
You'll have to work out how to handle cases when you have optional "Jr." or other names with more than three words. I suggest you read the doc I linked to for SUBSTRING_INDEX(), and also take the time to read about other string functions on that same page.
string_split() function, that produces a variable number of columns, MySQL doesn't have a solution for that. In general, SQL requires you to have an explicit expression for each column in the select-list. You can't make a query that appends more columns after the query has started reading data.The following example will reverse a 3-word string. You can take this example and write similar reversers for 2 and 4 words strings (in case your data is limited to 2-4 words). Then, you can just run 3 different queries, one for each amount of words (or maybe combine them all with unions) to handle all amounts of words.
As an example, run this to reverse the input Jackson Leroy Samuel to the output Samuel Leroy Jackson.
SELECT
CONCAT(SUBSTRING_INDEX('Jackson Leroy Samuel', ' ', - 1),
' ',
SUBSTR('Jackson Leroy Samuel',
INSTR('Jackson Leroy Samuel', ' ') + 1,
INSTR('Jackson Leroy Samuel', ' ')),
LEFT('Jackson Leroy Samuel',
LOCATE(' ', 'Jackson Leroy Samuel') - 1));
