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

 1 SET QUOTED_IDENTIFIER ON
 2 SET ANSI_NULLS ON
 3 GO
 4 --功能：分拆字符串 
 5 --参数：@String:要分拆的字符串；@Delimiter:分割符号 
 6 --返回值：@ValueTable结果表 
 7 create FUNCTION dbo.fn_Split( 
 8 @String nvarchar (4000), 
 9 @Delimiter nvarchar (10) 
10 ) 
11 RETURNS @ValueTable TABLE ([Value] NVARCHAR(4000),[id] int) 
12 BEGIN 
13 DECLARE @NextString nvarchar(4000), 
14 @Pos int, 
15 @NextPos int, 
16 @CommaCheck nvarchar(1),
17 @id int
18 
19 set @id=1 
20 
21 SET @NextString = '' 
22 SET @CommaCheck = right(@String,1) 
23 
24 SET @String = @String + @Delimiter 
25 
26 SET @Pos = CHARINDEX(@Delimiter,@String) 
27 SET @NextPos = 1 
28 
29 WHILE (@pos <> 0) 
30 BEGIN 
31 SET @NextString = SUBSTRING(@String,1,@Pos - 1) 
32 
33 INSERT INTO @ValueTable ( [Value],[id]) VALUES (@NextString,@id) 
34 
35 SET @String = SUBSTRING(@String,@pos +1,LEN(@String)) 
36 
37 SET @NextPos = @Pos 
38 SET @pos = CHARINDEX(@Delimiter,@String)
39 
40 set @id = @id +1
41 END 
42 
43 RETURN 
44 END
45 
46 GO

 

posted @ 2018-04-16 14:41  恝置  Views(601)  Comments(0)    收藏  举报