Change position of last name and rest of name in MySQL query

Question

My 'name' column has names stored like this

Lastname Firstname Middlename

I want to display it like this in my query:

Firstname Middlename Lastname

Not all records have a middle name, so they may be 1 or 2 spaces in the column

I have tried this :

SELECT CONCAT ( SUBSTRING_INDEX(`name`, ' ', 1) , ' ' , SUBSTRING_INDEX(`name`, ' ', -1) ) AS nicename`

But this only gives the last name. The "-1" part is not working...

Thanks for all help.

You will have a problem with some name composed name. Let's use someone famous `Leonardo Da Vinci` aka `Leonardo di ser Piero da Vinci`. You will end up with `Leonardo Vinci`. And this is way common than you think. Portuguese, Italian, some french firstname, ... — AxelH, Dec 16 '16 at 13:21
Just for the story, a Belgian had problem in US to register I can't remember to what official institution because her first name was `Marie Anne`. The space was not autorized but she can't be called `Marie` because this is not her name. — AxelH, Dec 16 '16 at 13:24

Rahul · Answer 1 · 2016-12-16T09:59:44.570

0

To start with, that's a bad design, Now, since your middlename part may be empty, you may bear with it and just select the name saying select name from tbl1.

Else, consider re-designing your table to have separate columns for Lastname, Firstname and Middlename and then You don't need all of this. You can just get data from all 3 columns saying

select concat(Firstname, Middlename, Lastname ) as nicename
from tbl1;

edited Dec 16 '16 at 09:59

answered Dec 16 '16 at 09:48

Rahul

76,197
13
71
125

@user2298034, post some sample data for the name column. – Rahul Dec 16 '16 at 10:25
Sample data from name column ( sorry I dont know how to format): ` Springsteen Bruce Dylan Bob Wodehouse Pelham Grenville Obama Barack Hussein ` – user2298034 Dec 16 '16 at 10:36

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

Found a solution :

 SELECT concat ( TRIM( SUBSTR(`name`, LOCATE(' ', `name`)) ), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(`name`, ' ', 1), ' ', -1) ) as nicename

Source: How to split the name string in mysql?

edited May 23 '17 at 11:53

Community

1
1

answered Dec 16 '16 at 13:12

user2298034

103
1
8

Change position of last name and rest of name in MySQL query

2 Answers2