create table test3001 ( id int, name nvarchar(200) ) insert into test3001 select 1 ,'A,B' union select 2 ,'C,D,E' select a.id,SUBSTRING(a.name,number+1,1) from test3001 a inner join master..spt_values b on b.type='p' and SUBSTRING(a.name,number+1,1)<>'' and SUBSTRING(a.name,number+1,1)<>','---------------------------- id ----------- ---- 1 A 1 B 2 C 2 D 2 E(5 行受影响)
select a.id,SUBSTRING(a.name+',',number+1,CHARINDEX(',',a.name)-1) from test3001 a inner join master..spt_values b on b.type='p' and LEN(SUBSTRING(a.name,number+1,CHARINDEX(',',a.name)))>0 and CHARINDEX(',',SUBSTRING(a.name+',',number+1,CHARINDEX(',',a.name)-1))=0
(
id int,
name nvarchar(200)
)
insert into test3001
select 1 ,'A,B'
union select 2 ,'C,D,E'
select a.id,SUBSTRING(a.name,number+1,1) from test3001 a
inner join master..spt_values b
on b.type='p' and SUBSTRING(a.name,number+1,1)<>'' and SUBSTRING(a.name,number+1,1)<>','----------------------------
id
----------- ----
1 A
1 B
2 C
2 D
2 E(5 行受影响)
inner join master..spt_values b
on b.type='p'
and LEN(SUBSTRING(a.name,number+1,CHARINDEX(',',a.name)))>0
and CHARINDEX(',',SUBSTRING(a.name+',',number+1,CHARINDEX(',',a.name)-1))=0