Update rows by separating values from one column and inserting into 2 diff. columns

Question

For eg. I have table T1. There are 4 columns in it c1 c2 c3 and c4. I have c1 as id, c2 contains combine name and address. c3 and c4 are empty. There are multiple rows for given id. Let's say there are 10 rows for id=10.

What I want is for all the rows with id=10, I want to read c2, separate values in c2 as name and address and store name in c3 and address in c4.

How can I do this in SQL server 2005/2008?

Thanks

Answer 1

Try:

UPDATE YourTable
    SET c3=LEFT(c2,CHARINDEX(' ',c2))
       ,c4=RIGHT(c2,LEN(c2)-CHARINDEX(' ',c2))
    WHERE c1=@YourIdValue

In the question, the method to separate values in c2 as name and address is not described, so I just split column c2 based on the first space found. c2='abcd efgh' becomes: c3='abcd', c4='efgh'.

Working sample:

DECLARE @YourTable table (c1 int,c2 varchar(10),c3 varchar(10),c4 varchar(10))
INSERT @YourTable VALUES (1,'aaaa bbbb',null,null)
INSERT @YourTable VALUES (1,'aaa bbb'  ,null,null)
INSERT @YourTable VALUES (1,'aa bb'    ,null,null)
INSERT @YourTable VALUES (1,'a b'      ,null,null)
INSERT @YourTable VALUES (2,'222 333'  ,null,null)
INSERT @YourTable VALUES (2,'aaa bbb'  ,null,null)
INSERT @YourTable VALUES (3,'a b'      ,null,null)

DECLARE @YourIdValue int
SET @YourIdValue=1

UPDATE @YourTable
    SET c3=LEFT(c2,CHARINDEX(' ',c2))
       ,c4=RIGHT(c2,LEN(c2)-CHARINDEX(' ',c2))
    WHERE c1=@YourIdValue

SELECT * FROM @YourTable

OUTPUT:

c1          c2         c3         c4
----------- ---------- ---------- ----------
1           aaaa bbbb  aaaa       bbbb
1           aaa bbb    aaa        bbb
1           aa bb      aa         bb
1           a b        a          b
2           222 333    NULL       NULL
2           aaa bbb    NULL       NULL
3           a b        NULL       NULL

(7 row(s) affected)