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
