declare @t varchar(1000)='1,2,3,4,5';;with tb as ( select SetID=CAST(SUBSTRING(@t,1,CHARINDEX(',',@t,1))AS varchar(8000)) ,SetID_split=CAST( stuff(@t+',',1,CHARINDEX(',',@t+','),'') AS varchar(8000)) union all select SetID=CAST( SUBSTRING(SetID_split,1,CHARINDEX(',',SetID_split,1))AS varchar(8000)) ,SetID_split=CAST( stuff(SetID_split,1,CHARINDEX(',',SetID_split+','),'') AS varchar(8000)) from tb where SetID_split>'' ) select replace(SetID,',','') SetID into #tb from tbselect * from test where SetID in (select SetID from #tb)
declare @t varchar(1000)='1,2,3,4,5';;with tb as
(
select SetID=CAST(SUBSTRING(@t,1,CHARINDEX(',',@t,1))AS varchar(8000))
,SetID_split=CAST( stuff(@t+',',1,CHARINDEX(',',@t+','),'') AS varchar(8000))
union all
select SetID=CAST( SUBSTRING(SetID_split,1,CHARINDEX(',',SetID_split,1))AS varchar(8000))
,SetID_split=CAST( stuff(SetID_split,1,CHARINDEX(',',SetID_split+','),'') AS varchar(8000))
from tb
where SetID_split>''
)
select replace(SetID,',','') SetID
into #tb
from tbselect *
from test
where SetID in (select SetID from #tb)