sql函数分割字符串，游标显示详细信息

 

 

-------------------建表--------------------------
use tttt
create table CursorTest
(
 id int identity(1,1),
 name varchar(20)
)
insert into CursorTest(name) values('xxxx')
insert into CursorTest(name) values('yyyyy')
insert into CursorTest(name) values('zzzzz')
insert into CursorTest(name) values('gggggg')
insert into CursorTest(name) values('hhhh')
insert into CursorTest(name) values('lllll')

create table StringSplit
(
 idd varchar(20)，
 countt int
)
insert into StringSplit values('1,2',0)
insert into StringSplit values('1,3',1)
insert into StringSplit values('1,2,3',2)

---------------------sql字符串分割---------------------------
USE [tttt]
GO
/****** 对象:  UserDefinedFunction [dbo].[f_split]    脚本日期: 02/24/2009 10:14:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   function   [dbo].[f_split](@c   varchar(2000),@split   varchar(2))--@c表示字符串,@split表示分隔符  
returns   @t   table(col   varchar(20)) ----返回一个表，用这个表来存储分割出来的所有数据 
as  
    begin  
      while(charindex(@split,@c)<>0)----当分隔符的索引不等于0时 
        begin  
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))----分割出来的单个字符  
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')----设置分割后的字符串  
        end  
      insert   @t(col)   values   (@c)  ----把分割出来的单个字符插入到表@t表
      return  
   
end  
----执行f_split函数
select * from f_split('1,2',',')

--------------------用游标来查出函数f_split返回table里每行字符为条件的详细信息-----------
alter PROCEDURE [dbo].[Test]
(@numm int)
AS
BEGIN
   select idd from StringSplit where  countt=@numm
 DECLARE LineCodeSelect CURSOR FOR ---声明LineCodeSelect游标
 select idd from StringSplit where countt=@numm---返回一个集合，或者是一条记录
 OPEN LineCodeSelect ---打开游标

DECLARE @LineCode VARCHAR(max)
FETCH NEXT FROM LineCodeSelect---从游标LineCodeSelect提取数据
INTO @LineCode
IF(@LineCode <> '')
BEGIN
SELECT id,name
  FROM dbo.f_split(@LineCode,',')
  LEFT JOIN CursorTest ON col = id
   SET @LineCode = ''
END
WHILE @@FETCH_STATUS = 0 ---fetch语句成功
BEGIN
  FETCH NEXT FROM LineCodeSelect
  INTO @LineCode
  IF(@LineCode <> '')
   BEGIN
   SELECT id,name
  FROM dbo.f_split(@LineCode,',')
  LEFT JOIN CursorTest ON col = id
   SET @LineCode = ''
   END
end
CLOSE LineCodeSelect---关闭游标

DEALLOCATE LineCodeSelect----释放游标
End

-----执行存储过程Test
exec Test 0