sql拆分字符串

--拆分字符卡
create FUNCTION s(@id varchar(20),@编号串 varchar(max))
RETURNS @tab table(ID varchar(20),编号 varchar(30))
begin
 declare @ipos int, @bh varchar(20);
 while 1>0
 begin
  set @ipos=Charindex(',',@编号串,1)
  if @ipos>0
   set @bh=substring(@编号串,1,@ipos-1)
  else
   set @bh=@编号串
  set @编号串=substring(@编号串,@ipos+1,len(@编号串))
  if LEN(@bh)>0
      begin
     insert into @tab(ID,编号)values(@id,@bh);
   end
  if isnull(@ipos,0)<1 break
 end
 return
end

--循环获取数据(存储过程版本)
alter proc c
as
begin
    create table #w
    (
      id varchar(20),
      name varchar(20)
   
    )
  declare @编号 varchar(max)
  declare @id varchar(max)
  select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* into #T from ss
  declare @i int;
  set @i=1;
  while(@i<=(select count(*) from ss))
   begin
   set @编号=(select  name from #T where RowNumber=@i)
   set @id=(select  id from #T where RowNumber=@i)
   insert into #w select * from s(@id,@编号)
   set @i=@i+1
   end
   select * from #w
  drop table #T
  drop table #w
end
--exec c

--循环获取数据(函数版本)
alter FUNCTION s1()
     RETURNS @tab table(ID varchar(20),编号 varchar(30))
begin
 begin
   declare @编号 varchar(max)
   declare @id varchar(max)
   declare @i int;
   set @i=1;
   while(@i<=(select count(*) from ss))
    begin
     set @编号=(
        select  name from
        (
           select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* from ss
        )a where a.RowNumber=@i
              )
     set @id=(
                 select  id from
                 (
                    select ROW_NUMBER() OVER(ORDER BY getdate()) AS RowNumber ,* from ss
                 )b
                 where b.rowNumber=@i
             )
    insert into @tab select * from s(@id,@编号)
   set @i=@i+1
    end
 end
 return
end
--select * from s1()

ss表结构如下

ID,name两列