1 ALTER FUNCTION [dbo].[fnt_SplitString]
2 (
3 @p1 varchar(Max),
4 @p3 varchar(255)
5 )
6 RETURNS
7 @Table_Var TABLE
8 (
9 c1 varchar(max)
10 )
11 AS
12 BEGIN
13 declare @p2 varchar(max)
14 set @p2=rtrim(ltrim(@p1))
15 declare @pos1 int
16 declare @pos2 int
17 set @pos1=1
18 set @pos2=1
19 while (@pos1<len(@p2))
20 begin
21 set @pos1=charindex(@p3,@p2)
22 if (@pos1=0)
23 begin
24 insert into @table_var values(@p2)
25 set @pos1=len(@p2)
26 end
27 else
28 begin
29 insert into @table_var values(left(@p2,@pos1-1))
30 set @p2=right(@p2,len(@p2)-@pos1)
31 set @pos1=0
32 end
33 end
34 RETURN
35 END
36
37 '调用方式
38 Select C1,(Row_Number() Over(Order By @@Cursor_Rows)) As C2 From dbo.Fnt_SplitString('ID,WBS,Quantity,MPSNO,Attribute,FileContent,MaterielName,MaterielCode,ExportAccount',',')
方法二：
1 ALTER function [dbo].[SplitString]
2 (
3 @Input nvarchar(max),
4 @Separator nvarchar(max)=',',
5 @RemoveEmptyEntries bit=1
6 )
7 returns @TABLE table
8 (
9 [Id] int identity(1,1),
10 [Value] nvarchar(max)
11 )
12 as
13 begin
14 declare @Index int, @Entry nvarchar(max)
15 set @Index = charindex(@Separator,@Input)
16
17 while (@Index>0)
18 begin
19 set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
20
21 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
22 begin
23 insert into @TABLE([Value]) Values(@Entry)
24 end
25
26 set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
27 set @Index = charindex(@Separator, @Input)
28 end
29
30 set @Entry=ltrim(rtrim(@Input))
31 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
32 begin
33 insert into @TABLE([Value]) Values(@Entry)
34 end
35
36 return
37 end
38
39
40 Select * From [dbo].[SplitString]('ID,WBS,Quantity,MPSNO,Attribute,FileContent,MaterielName,MaterielCode,ExportAccount',',',1)