SQL 中拆分字符串

while patindex('%,%',@pid)>0
begin
set @pid_after=substring(@pid,PATINDEX('%,%',@pid)+1,len(@pid))
set @p=substring(@pid,0,len(@pid)-len(@pid_after))
set @strSQL='update  +' set  +'=getdate(),'+@state+' where + @p
exec(@strSQL)
set @pid=@pid_after
print @strSQL
end
set @strSQL='update  +' set  +'=getdate(),'+@state+' where + @pid
print @strSql
--------------------------------------------------方法2：动态SQL法------------------------------------------
declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''
select @sql
exec (@sql)
------------------------------------------------方法3：循环截取法---------------------------------------------
declare @s varchar(100),@len int,@split varchar(2)
set @s='1,2,3,4,5,6,7,8,9,10'
set @split=','
set @len=len(@split+'a')-2
while charindex(@split,@s)>0
begin
print left(@s,charindex(@split,@s)-1)
set @s=stuff(@s,1,charindex(@split,@s)+@len,'')
end
print @s