sql server - SQL command to map data from one column to another -

suppose table looks this

[   name, number, firstname]     [foo:bar,    123,          ] [bar:foo,    456,          ] 

i want split name field @ ':' , use first part 'firstname' column. furthermore, want permanent, rather result set. how that?

i'm using sql server 2008.

i'm aware variants of question have been asked before, seem deal merely selecting data, , mean update it.

you create view use view future selects.

create view split name select  substring(name, 1, charindex(':',  name, 1) - 1) firstname,  rtrim(substring(name,  charindex(':',  name, 1)+1,100)) surname, number mytable 

alternatively,

update mytable set firstname = substring(name, 1, charindex(':',  name, 1) - 1)  mytable