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.
– lc., Commented Dec 31, 2013 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.
– Bill N., Commented Dec 31, 2013 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
– Farid Rn, Commented Dec 31, 2013 at 17:27
can you install mysql libraries like github.com/mysqludf/lib_mysqludf_preg#readme ? — Digital Chris
– Digital Chris, Commented Dec 31, 2013 at 17:28
@DigitalChris I'm not sure. I thought I would find a simple and fast SQL query for my problem. — Farid Rn
– Farid Rn, Commented Dec 31, 2013 at 17:32

Mike Harrison · Accepted Answer · 2018-05-25 19:58:11Z

6

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;

answered May 25, 2018 at 19:58

Mike Harrison

1,40314 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2013-12-31 18:02:47Z

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, 2013 at 18:02

answered Dec 31, 2013 at 17:27

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

5 Comments

Farid Rn Over a year ago

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.

Bill N. Over a year ago

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.

Farid Rn Over a year ago

I added another row to your example and you can see it's not working well. sqlfiddle.com/#!2/e2b03e/1

Rahul Over a year ago

@faridv, left desk for time being. see my edit and it will work now irrespective of how many part is present in the name.

Farid Rn Over a year ago

Your second query seems to be working, I need to give it a try.

Digital Chris · Accepted Answer · 2013-12-31 17:40:35Z

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, 2013 at 17:40

Digital Chris

6,1771 gold badge22 silver badges29 bronze badges

4 Comments

Farid Rn Over a year ago

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.

Digital Chris Over a year ago

Thanks to @Rahul for the sqlfiddle setup.

Farid Rn Over a year ago

My names are in Persian and they don't have middle names in them. They have names and multi-part family names.

Digital Chris Over a year ago

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.

Collectives™ on Stack Overflow

Explode and implode strings in mysql query

3 Answers 3

Comments

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related