sql server 字符串根据指定分隔符进行分组

 SET QUOTED_IDENTIFIER ON

 SET ANSI_NULLS ON

 GO

 --功能：分拆字符串

 --参数：@String:要分拆的字符串；@Delimiter:分割符号

 --返回值：@ValueTable结果表

 create FUNCTION dbo.fn_Split(

 @String nvarchar (4000),

 @Delimiter nvarchar (10)

 )

 RETURNS @ValueTable TABLE ([Value] NVARCHAR(4000),[id] int)

 BEGIN

 DECLARE @NextString nvarchar(4000),

 @Pos int,

 @NextPos int,

 @CommaCheck nvarchar(1),

 @id int

 set @id=1 

 SET @NextString = ''

 SET @CommaCheck = right(@String,1) 

 SET @String = @String + @Delimiter 

 SET @Pos = CHARINDEX(@Delimiter,@String)

 SET @NextPos = 1 

 WHILE (@pos <> 0)

 BEGIN

 SET @NextString = SUBSTRING(@String,1,@Pos - 1) 

 INSERT INTO @ValueTable ( [Value],[id]) VALUES (@NextString,@id) 

 SET @String = SUBSTRING(@String,@pos +1,LEN(@String)) 

 SET @NextPos = @Pos

 SET @pos = CHARINDEX(@Delimiter,@String)

 set @id = @id +1

 END 

 RETURN

 END

 GO