CODEWITHSUNDEEP
mysqlsqlsplit
Petru Lebada
Petru Lebada

Reputation: 1682

Adding a new column,splitting a column value in two then insert a part of the value in the new column

I have a table with a column 'Name', like the following:

    Name
Michael Jackson
Chester Bennington
Mike Shinoda

I want to split the 'Name' values in FirstName and LastName,blike this:

LastName          FirstName
Jackson          Michael
Bennington       Chester
Shinoda          Mike

And also I need to create new columns and add those values, all in a single query if its possible. So as a review, I have Name, I need to create LastName, FirstName columns, split the Name values after the first blank space,the value before the blank space will go into LastName and the values after the blank space will go into FirstName. This is what I have tried so far:

ALTER TABLE `data` ADD `FirstName` VARCHAR(30),ADD `LastName` VARCHAR(30)  
INSERT INTO `data`(`LastName`,`FirstName`) VALUES ('...','...')

Help please..

Upvotes: 0

Views: 4029

Answers (4)

Malhar Lakdawala
Malhar Lakdawala

Reputation: 11

INSTR shows error, so used POSITION. I am trying to split the columns at character '/':

UPDATE pincode
   SET latitude = LEFT( coordinates, POSITION('/' IN coordinates) - 1 )
     , longitude = RIGHT( coordinates, LENGTH(coordinates) - POSITION('/' IN coordinates) )
 WHERE coordinates LIKE '%/%';

Upvotes: 0

rasso
rasso

Reputation: 2221

Try this

UPDATE data SET FirstName = SUBSTRING_INDEX(Name, ' ', 1), LastName = SUBSTRING(SUBSTRING_INDEX(Name, ' ', 2),
       LENGTH(SUBSTRING_INDEX(Name, ' ', 1)) + 1)

EDIT

As David Faber stated, one can use SUBSTRING_INDEX(Name, ' ', -1) for updating the LastName column.

Upvotes: 3

David Faber
David Faber

Reputation: 12485

You might try this:

ALTER TABLE `data` ADD `FirstName` VARCHAR(30)
                 , ADD `LastName` VARCHAR(30);

UPDATE `data`
   SET `FirstName` = SUBSTR( `Name`, 1, INSTR(`Name`, ' ') - 1 )
     , `LastName` = SUBSTR( `Name`, INSTR(`Name`, ' ') + 1, LENGTH(`Name`) - INSTR(`Name`, ' ') )
 WHERE `Name` LIKE '% %';

I added the WHERE clause with LIKE operator to the update statement; otherwise it could fail if Name has any values without spaces since INSTR() will return 0 for these.

Alternately, instead of SUBSTR() you might use LEFT() and RIGHT()`:

UPDATE `data`
   SET `FirstName` = LEFT( `Name`, INSTR(`Name`, ' ') - 1 )
     , `LastName` = RIGHT( `Name`, LENGTH(`Name`) - INSTR(`Name`, ' ') )
 WHERE `Name` LIKE '% %';

Hope this helps.

Upvotes: 2

erakm
erakm

Reputation: 106

This is your table. You use it how you want. Try this

select * from
(
select SUBSTRING ( name ,(select CHARINDEX(' ',name, 0) from table1)+1 , (select LEN(name) from table1)-(select CHARINDEX(' ',name, 0) from table1)-1) as lastname,
SUBSTRING ( name ,0 , (select LEN(name) from table1)-(select CHARINDEX(' ',name, 0) from table1)) as firstname
from table1;
)

Upvotes: -1

Related Questions