CODEWITHSUNDEEP
mysqlsql
Robert Dickey
Robert Dickey

Reputation: 834

Update that splits one column into two?

I'm in a bit of a time crunch. I have a table with 2 columns. firstName, and lastName.

The data imported into SQL had both first and last name inside of the firstName column.

Is there a quick way to update the table to put everything before the first space into the lastName, and everything after the space into the firstName column? I know I could export, and do this via excel, but it in close to production time so I would rather not risk issues.

I have looked at a couple different split posts on here, but those do not involve updating at the same time.

Upvotes: 1

Views: 185

Answers (2)

Christian
Christian

Reputation: 827

Try to use:

UPDATE
   table
SET
   lastname = SUBSTRING(firstName, 1, CHARINDEX(' ', firstName) - 1),
   firstName= SUBSTRING(firstName, CHARINDEX(' ', firstName) + 1, LEN(firstName))

Upvotes: 2

1000111
1000111

Reputation: 13519

MySQL

Demonstration:

SET @str := 'Robert Optional Dickey';

SELECT 
SUBSTRING_INDEX(@str,SUBSTRING_INDEX(@str,' ',-1),1) AS lastName,
SUBSTRING_INDEX(@str,' ',-1) AS firstName;

Output:

lastName            firstName
Robert Optional     Dickey

Update Query:

UPDATE your_table
SET lastName = SUBSTRING_INDEX(@str,SUBSTRING_INDEX(@str,' ',-1),1),
firstName = SUBSTRING_INDEX(@str,' ',-1);

Note: It will work for any number spaces inside the full name.It just considers the string after the last space as first name.

Upvotes: 1

Related Questions