Explode and implode strings in mysql query

Question

+------+-----------------+
|  id  |      name       +
+------+-----------------+
|  1   | David Gilmour   |
|  2   | Roger Waters    |
|  3   | Li Jen Ho       |
+------+-----------------+

Current format of names is firstName lastName and I want to change it to lastName firstName and I want to that in one database query.

The solution I've in my mind is to split names by space, reversing items and then imploding them again with space glue. But I don't know how to write a query for that.

I can simply do that in PHP by exploding names by space and putting them inside an array, reversing the array and then imploding the words by space glue but currently I don't have access to PHP.

Update 1: I found this similar question but I can't make it work. I hope I'm not asking a duplicate question.

Update 2: Names can have more than 2 parts. I assume only the first word is first name and the rest of name is last name.

Well what do you have access to? awk would do this in a second, Word can do it as well with a find-replace, Excel formulas can help... Otherwise if you're going to store names in a database and the parts have meaning (like "last name" and "first name"), you should really keep two columns to avoid issues like this. — lc., Dec 31 '13 at 17:25
I wouldn't assume he's using the Microsoft Office suite at all. The tags specifically say 'mysql.' He also talks about using PHP which would suggest he's writing a server-side web script. — Bill N., Dec 31 '13 at 17:27
@lc. The server I'm working on has only mysql and I can't install php on it! creating 2 columns for "first name" and "last name" is a good idea but I still have problems by splitting names. — Farid Rn, Dec 31 '13 at 17:27
can you install mysql libraries like https://github.com/mysqludf/lib_mysqludf_preg#readme ? — Digital Chris, Dec 31 '13 at 17:28
@DigitalChris I'm not sure. I thought I would find a simple and fast SQL query for my problem. — Farid Rn, Dec 31 '13 at 17:32
I've commented on the below post to handle your additional criteria. I'm not the type of person that just writes out the answer for someone, so you should try to put it together yourself or just wait for some desperate soul to come in and do the worst things you can do for a developing programmer. — Bill N., Dec 31 '13 at 17:35
@BillN. I'm not sure if I put it together, I can get better results than those desperate souls! :D Any kind help is appreciated, even if it doesn't work for me, it can be a huge help for someone else. — Farid Rn, Dec 31 '13 at 17:38

score 4 · Answer 1 · answered May 25 '18 at 19:58

I know I am late to the party but there is a better solution that can handle an unknown number of results.

GROUP_CONCAT

SELECT student_name,
  GROUP_CONCAT(DISTINCT test_score
               ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;

Rahul · Accepted Answer · 2013-12-31T18:02:47.667

2

Try this:

select concat(last_name," ",first_name) as FullName
from
(
select SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) AS last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from your_table
) tab

See a sample here

http://sqlfiddle.com/#!2/cd4ee/4

EDIT:

Then a slight modified version will do your work. You can refer the same fiddle for a updated sample as per your need.

select concat(last_name," ",first_name) as FullName
from
(
select right(name,(length(name) - instr(name,' '))) as last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from tab
) tab1

edited Dec 31 '13 at 18:02

answered Dec 31 '13 at 17:27

Rahul

76,197
13
71
125

Your solution looks promising but I made an update to my question. names can have more than two parts and I want to keep the first part as first name and the rest of string as last name. – Farid Rn Dec 31 '13 at 17:31
2

While this works for all of the criteria he specified, if the OP wants it to be properly expandable, they should consider making the indices more dynamic to support middle names, etc, etc. Consider using `LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))` as a means of determining how many "words" you have in the full name and then handle the results accordingly. – Bill N. Dec 31 '13 at 17:32
I added another row to your example and you can see it's not working well. http://sqlfiddle.com/#!2/e2b03e/1 – Farid Rn Dec 31 '13 at 17:34
@faridv, left desk for time being. see my edit and it will work now irrespective of how many part is present in the name. – Rahul Dec 31 '13 at 18:04
Your second query seems to be working, I need to give it a try. – Farid Rn Dec 31 '13 at 18:28

score 0 · Answer 3 · answered Dec 31 '13 at 17:40

0

Here it is working with 3 part names: http://sqlfiddle.com/#!2/e2b03e/7

SELECT 
   SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name,
   If(  length(name) - length(replace(name, ' ', ''))>1,  
   SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) ,NULL) 
           as middle_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 3), ' ', -1) AS last_name
from tab

In a comment you mention wanting to put middle and last name together into one field, but don't do that. That just continues the bad practice, and you will no doubt end up with names sorted by middle name, etc.

answered Dec 31 '13 at 17:40

Digital Chris

6,177
1
20
29

First of all thanks for your answer. second of all, your answer is the same answer that I mentioned in my question that doesn't work for me. And third of all Names can be even more than 3 parts. I need a solution that covers any sort of given name. – Farid Rn Dec 31 '13 at 17:43
Thanks to @Rahul for the sqlfiddle setup. – Digital Chris Dec 31 '13 at 17:44
My names are in Persian and they don't have middle names in them. They have names and multi-part family names. – Farid Rn Dec 31 '13 at 17:46
OK, there's still nothing wrong with using the above code and inserting first_name into one field and "Middle_name Last_name" into another... or updating it all back into one name field, but don't do that if you can avoid it. – Digital Chris Dec 31 '13 at 17:53

Explode and implode strings in mysql query

3 Answers3