How do I flip a column in mysql that has data structured: 'last name, first name'?

Question

I want to create a new column in a MYSQL table that has Fn Ln instead of Ln, Fn. Most of my data is printed Fn Ln.

Another idea is to incorporate a string function each time there is an output (php based) but that seems to waste resources. Finally, I couldn't find the syntax (loop or foreach) for my php function anyway.

Here is the working php function that I got from a previous post:

   $name = "Lastname, Firstname";
    $names = explode(", ", $name);
    $name = $names[1] . " " . $names[0];

Answer 1

Have a look at using

SUBSTRING_INDEX(str,delim,count)

RIGHT(str,len)

And

LEFT(str,len)

CONCAT(str1,str2,...)

Also, why are these fields stored in a single column in the first place? Would it not have been easier if they were stored in the correct Fields FirName, and separate column LastName?

Im a bit more of a SQL Server but you can try something like

SELECT  CONCAT( RIGHT(FirstNameLastName,LEN(FirstNameLastName) - SUBSTRING_INDEX(FirstNameLastName, ' ', 1)),
                ', ',
                LEFT(FirstNameLastName,SUBSTRING_INDEX(FirstNameLastName, ' ', 1) - 1)
                )
FROM    YourTable

EDIT:

Sql Server would have looked like (interest sakes)

DECLARE @Table TABLE(
        FirstNameLastName VARCHAR(100)
)

INSERT INTO @Table SELECT 'Foo Bar'

SELECT  RIGHT(FirstNameLastName,LEN(FirstNameLastName) - PATINDEX('% %', FirstNameLastName)) + ', ' + LEFT(FirstNameLastName,PATINDEX('% %', FirstNameLastName) - 1)
FROM    @Table

Answer 2

You can use the mysql function SUBSTRING_INDEX to get a portion of a string up to an specified number or occurrences.

Make sure to change anything I've prefixed with a $ to what you want it called

# Add new column
alter table $TABLE add $newField varchar(40); # or something like that

# Populate new data
update $TABLE set $newField = CONCAT(
  SUBSTRING_INDEX(SUBSTRING_INDEX($oldField,',',2),',',-1),
  ' ',
  SUBSTRING_INDEX($oldField,',',1)
);

That splits the name field on , and puts the second part first, in the new field you created.