TSQL高手请进：更新表-增加一列 （从其他列选取字符)

Question

ALTER TABLE TB ADD Name VARCHAR(20)
UPDATE TB
SET Name=SUBSTRING(Detail,CHARINDEX('Name:',Detail)+5,CHARINDEX('+ED',Detail)-CHARINDEX('Name:',Detail,CHARINDEX('Name:',Detail))-5)可以这样

Answer 1

create table Table1
(ID int,Detail varchar(30))insert into Table1
 select '1','xxxxxName:Zhao+EDxxxxx' union all
 select '2','xxxName:Qian+EDxxxx' union all
 select '3','yyyName:Sun+EDxxxx' union all
 select '4','zzzzzName:Li+EDxxx'
 -- 增加一列Name
alter table Table1 add Name varchar(30)update Table1 set Name=
       substring(Detail,
                 charindex('Name:',Detail,1)+5,
                 charindex('+ED',Detail,1)-charindex('Name:',Detail,1)-5)
-- 结果
select * from Table1/*
ID          Detail                         Name
----------- ------------------------------ ------------------------------
1           xxxxxName:Zhao+EDxxxxx         Zhao
2           xxxName:Qian+EDxxxx            Qian
3           yyyName:Sun+EDxxxx             Sun
4           zzzzzName:Li+EDxxx             Li(4 row(s) affected)
*/

Answer 2

update Table1 set Name
  =stuff(left(detail,charindex('+',detail)-1),1,CHARINDEX('name',detail)+4,'')