0

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.

Mark Lewis
  • 157
  • 2
  • 13
  • You should consider splitting up the data in 2 columns: first and last name. – juergen d Jan 07 '18 at 19:38
  • Not particularly a good idea to do this in mysql. RDBMSs are not really efficient in text processing. You are better off creating an additional field and use a program to change the order of the words around and save the results in the new field. – Shadow Jan 07 '18 at 19:46
  • dump into a spreadsheet and split the name and update back to mysql. if this is a one-off. – RoMEoMusTDiE Jan 07 '18 at 19:53
  • You should read [this](http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/) – HoneyBadger Jan 07 '18 at 19:55
  • @HoneyBadger 100% agree. However, I'm working with an existing project and dataset. Unfortunately, the ambiguity will likely remain. – Mark Lewis Jan 07 '18 at 20:03
  • Have a look at https://stackoverflow.com/questions/7684818/reverse-the-order-of-words-in-tsql Though that is for TSQL the MySQL approach should be very similar. – fauxmosapien Jan 07 '18 at 20:04
  • @Shadow I did not know that they were inefficient with text processing. Definitely something to think about. Sounds like I should add a hook on save and have a special column for sorting to save what I need. – Mark Lewis Jan 07 '18 at 20:05

2 Answers2

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Downvoter: Please explain why you think this answer is not good. Perhaps I can improve it. – Bill Karwin Jan 07 '18 at 23:03
  • Adding a more generic version would be great. Meaning, when we have a variable number of words! – Enissay Oct 11 '22 at 08:52
  • 1
    @Enissay, If you're looking for some kind of `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. – Bill Karwin Oct 11 '22 at 15:51
1

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));
Tomer Shay
  • 771
  • 6
  • 17