拆分: declare @a varchar(100)set @a='1,2,3,4'select v, SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) from ( select @a v )t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.v,s.number,1) = ','
给你个示例参照下declare @string varchar(max),@key varchar(20) set @string = 'a,b,c,d,e,f,zzzz,123' set @key =',' ; with a as ( select @string a ),n as ( select number from master..spt_values where type = 'P' ) select *,SUBSTRING(a, number, CHARINDEX(@key, a + @key, number) - number) AS element from a join n on n.number <=len(a.a) and SUBSTRING(@key+a.a,number,1) =@key
declare @a varchar(100)set @a='1,2,3,4'select
v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number)
from
(
select @a v
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
给你个示例参照下declare @string varchar(max),@key varchar(20)
set @string = 'a,b,c,d,e,f,zzzz,123'
set @key =','
;
with a as
(
select @string a
),n as
(
select number from master..spt_values
where type = 'P'
)
select *,SUBSTRING(a, number, CHARINDEX(@key, a + @key, number) - number) AS element
from a join n on n.number <=len(a.a) and SUBSTRING(@key+a.a,number,1) =@key