Search code examples
mysqlpymysql

Split names into first and last and insert into existing first Name column and last Name column sql

I have a table where a column is named CustomerName that contains first name, middle initial and last name. Some names might not have a middle initial. In the same table, i have 3 columns named firstName, middleName and LastName thats populated with None values. I need to split the CustomerName column and put them in respective columns. How would I do that in mysql? I tried the following but get error:

UPDATE xcust SET
LastName = SUBSTRING_INDEX(trim(CustomerName),' ',-1) 
case 
when LENGTH(trim(CustomerName)) - LENGTH(REPLACE(trim(CustomerName), ' ', 
'')) = 1 then null
else  MiddleName = SUBSTRING_INDEX(SUBSTRING_INDEX(trim(CustomerName),' 
',2),' ',-1) 
end
FirstName = SUBSTRING_INDEX(trim(CustomerName),' ',1)
where CustomerName = 'JAMES G STAHL'"""

The error i receive is:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'case \nwhen LENGTH(trim(CustomerName)) - LENGTH(REPLACE(trim(CustomerName), ' ', ' at line 3")

Im using pymysql to run the query. Any help would be appreciated.

Solution

  • Not tested, but at first glance you are missing commas between the different values you are setting and need to take MiddleName outside of the case statement.

    UPDATE xcust SET
LastName = SUBSTRING_INDEX(trim(CustomerName),' ',-1),
MiddleName = 
    case when LENGTH(trim(CustomerName)) - LENGTH(REPLACE(trim(CustomerName), ' ', '')) = 1 then
        null
    else
        SUBSTRING_INDEX(SUBSTRING_INDEX(trim(CustomerName),' ',2),' ',-1)
    end,
FirstName = SUBSTRING_INDEX(trim(CustomerName),' ',1)
where CustomerName = 'JAMES G STAHL'