SELECT
SUBSTRING(Detail,CHARINDEX('Name:',Detail)+5,CHARINDEX('+ED',Detail)-CHARINDEX('Name:',Detail,CHARINDEX('Name:',Detail))-5)
FROM Table1
--如果有好几个,同一行里,这边会只取第一个符合的情况这个昨天不是已经处理了吗?有什么不妥的地方,或者别人理解错的地方,你可以指出来~
如果你什么也不说,问题也不好解决啊
SUBSTRING(Detail,CHARINDEX('Name:',Detail)+5,CHARINDEX('+ED',Detail)-CHARINDEX('Name:',Detail,CHARINDEX('Name:',Detail))-5)
FROM Table1
--如果有好几个,同一行里,这边会只取第一个符合的情况这个昨天不是已经处理了吗?有什么不妥的地方,或者别人理解错的地方,你可以指出来~
如果你什么也不说,问题也不好解决啊
-- 好像昨天有这个问题create table t(id int , detail varchar(30))
go
insert into t(id , detail)
select 1 , 'xxx+xxName:Zhao+xxxxx' union all
select 2 , 'xxxName:Qian+xxxx+' union all
select 3 , '+yyyName:Sun+xxxx' union all
select 4 , 'zzzzzName:Li+xxx'
go
with m as (
select id , SUBSTRING(detail,charindex(':',detail) + 1 ,30) detail from t
)
select id , SUBSTRING(detail ,0, charindex('+',detail)) from m
go
drop table t
go
(4 行受影响)
id
----------- ------------------------------
1 Zhao
2 Qian
3 Sun
4 Li(4 行受影响)
SUBSTRING(
Detail
,CHARINDEX('Name:',Detail)+5
,CHARINDEX('+',Detail,CHARINDEX('Name:',Detail)+5)-CHARINDEX('Name:',Detail,CHARINDEX('Name:',Detail))-5)
FROM Table1已修改,取仅跟着Name的那个
create table Table1
(ID int,Detail varchar(50))insert into Table1
select 1,'xxx+xxName:Zhao+xxxxx' union all
select 2,'xxxName:Qian+xxxx+' union all
select 3,'+yyyName:Sun+xxxx' union all
select 4,'zzzzzName:Li+xxx'
select substring(Detail,
charindex('Name:',Detail,1)+5,
charindex('+x',Detail,charindex('Name:',Detail,1)+5)-charindex('Name:',Detail,1)-5
) 'Detail'
from Table1
/*
Detail
--------------------------------------------------
Zhao
Qian
Sun
Li(4 row(s) affected)
*/
ID; Detail; Name
1; xxx+xxName:Zhao+xxxxx;Zhao
2; xxxName:Qian+xxxx+;Qian
3; +yyyName:Sun+xxxx;Sun
4;zzzzzName:Li+xxx;Li
alter table table1 add Name varchar(20)
Update table1 set Name = ???
Detail
,CHARINDEX('Name:',Detail)+5
,CHARINDEX('+',Detail,CHARINDEX('Name:',Detail)+5)-CHARINDEX('Name:',Detail,CHARINDEX('Name:',Detail))-5)我的不可以吗?
create table Table1
(ID int,Detail varchar(50))insert into Table1
select 1,'xxx+xxName:Zhao+xxxxx' union all
select 2,'xxxName:Qian+xxxx+' union all
select 3,'+yyyName:Sun+xxxx' union all
select 4,'zzzzzName:Li+xxx'
alter table Table1 add Name varchar(20)update Table1 set Name=
substring(Detail,
charindex('Name:',Detail,1)+5,
charindex('+x',Detail,charindex('Name:',Detail,1)+5)-charindex('Name:',Detail,1)-5)
-- 结果
select * from Table1/*
ID Detail Name
----------- -------------------------------------------------- --------------------
1 xxx+xxName:Zhao+xxxxx Zhao
2 xxxName:Qian+xxxx+ Qian
3 +yyyName:Sun+xxxx Sun
4 zzzzzName:Li+xxx Li(4 row(s) affected)
*/