Question: 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..

Answer: 
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.
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;
)

```