How to separate a comma separated name field in MySQL in to first name, middle name, last name and fullname?

Question

I have a NAME column and the data is stored like this in a MySql database:

Doe,John P

I would like to break it out into FNAME LNAME MNAME and FULLNAME.

If I don't want to tamper with the SQL table, should I create a view?

I know how to fix it in PHP with a function but I would rather the processing be done on the database server than the application server.

This is what I have so far. I thinking I need a compound query or something similar.

SELECT Name
       , SUBSTRING_INDEX(Name,',',-1) As FNAME
       , SUBSTRING_INDEX(Name,',',1)  As LNAME
       , SUBSTRING_INDEX(Name,' ',1)  As MNAME 
  FROM people;

I'm having trouble grabbing the middle name and doing what I think should be CONCAT function to get FULLNAME.

It would be great if there was a way (maybe a conditional) to handle names in the database that didn't have middle names. (i.e. DOE,JOHN).

Check out this question [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns). — Bla..., Apr 09 '15 at 02:19

score 0 · Answer 1 · answered Apr 09 '15 at 02:22

A view is a fine way to get the results you want for future queries. The defining query would be:

SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ',', -1), ' ', 1)  As FNAME,
       SUBSTRING_INDEX(Name, ',', 1)  As LNAME,
       SUBSTRING_INDEX(Name, ' ', -1)  As MNAME
FROM people;

This will work assuming that the format is exactly correct as you describe it in the table. Small changes -- such as no middle name or a space after the comma -- would make the syntax more complicated.

How to separate a comma separated name field in MySQL in to first name, middle name, last name and fullname?

1 Answers1