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)