SQL Server: select value, split on a delimiter, then update two columns

Question

I have a SQL Server 2008 database table with three varchar Columns - Col1, Col2, Col3. Col1 has data in it with a single space in between, Col2 and Col3 are empty.

I need to write a query to select the data from Col1, break up each value using the space as the delimiter, and inserting the data on either side of the space into Col2 and Col3 respectively.

I am not too sure how to proceed. Can this be accomplished in SQL, or should I create a small program to do the work for me? I'd appreciate a pointer in the right direction if this can be accomplished via SQL.

Thanks.

Answer 1

UPDATE table SET 
  Col2 = SUBSTRING(Col1, 1, CHARINDEX(' ', Col1)-1), 
  Col3 = SUBSTRING(Col1, CHARINDEX(' ', Col1)+1, 8000)
WHERE Col1 LIKE '% %';

Answer 2

If you can guarantee there is only one space:

create table #temp (col1 varchar(50),col2 varchar(50), col3 varchar(50))

insert into #temp (col1)
select 'test 1'
union all
select 'test 2'
union all 
select 'test 3'

update #temp
set col2 = left(col1, charindex (' ', col1)),
col3 = substring(col1,charindex (' ', col1)+1, len(col1))
from #temp