CODEWITHSUNDEEP
mysqlselectsubstring
Benshack
Benshack

Reputation: 137

mySQL - Separate Lastname,Firstname and CompanyName entries from a single column

I've got a column in a database which contains company names, and customer names... what I'd like to do is keep the CompanyName column completely intact, but wherever there is a comma in the CompanyName I'd like to take that information and populate it into a FirstName and LastName field. So that basically...

(each number represents a different row in the table)

Before:

CompanyName Column:
1. Big Company Inc
2. Smith, John
3. Sue, Maggie

After:

CompanyName Column:
1. Big Company Inc
2. Smith, John
3. Sue, Maggie

LastName Column:
1.
2. Smith
3. Sue

FirstName Column:
1.
2. John
3. Maggie

This one is pretty dang tricky for me... Any help is greatly appreciated!

Upvotes: 1

Views: 726

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this:

select id, companyName, if(companyName like '%,%', substring_index(companyname, ',', -1), '') firstName, 
       if(companyName like '%,%', substring_index(companyname, ',', 1), '') lastName
from company;

Upvotes: 1

Related Questions