create table #temp ( ss varchar(200) not null ) declare @str varchar(200) declare @result varchar(1000) set @str='aaa,bb,c,d,e,ffffff' set @result =' insert into #temp(ss) select '''+replace(@str,',','''union select''')+'''' exec(@result) select * from #temp
2.利用charindex和substring
create table #temp ( ss varchar(200) not null ) declare @str varchar(200) declare @curr int declare @prev int set @str='aaa,bb,c,d,e,ffffff' set @curr=1 set @prev=1 while @prev < len(@str) begin set @curr=charindex(',',@str,@prev) if @curr>@prev insert #temp select substring(@str,@prev,@curr-@prev) else begin insert #temp select substring(@str,@prev,len(@str)-@prev+1) break end set @prev=@curr+1 end select * from #temp