SQL Server 自定义字符串分割函数

 1 create function Func_StrArrayLength   2 (   3   @str varchar(1024),  --要分割的字符串  4   @split varchar(10)  --分隔符号  5 )   6 returns int   7 as   8 begin   9   declare @location int  10   declare @start int  11   declare @length int  12   13   set @str=ltrim(rtrim(@str))  14   set @location=charindex(@split,@str)  15   set @length=1  16   while @location<>0  17   begin  18     set @start=@location+1  19     set @location=charindex(@split,@str,@start)  20     set @length=@length+1  21   end  22   return @length  23 end24 go

 1 create function Func_StrArrayStrOfIndex   2 (   3   @str varchar(1024),  --要分割的字符串  4   @split varchar(10),  --分隔符号  5   @index int --取第几个元素  6 )   7 returns varchar(1024)   8 as   9 begin  10   declare @location int  11   declare @start int  12   declare @next int  13   declare @seed int  14   15   set @str=ltrim(rtrim(@str))  16   set @start=1  17   set @next=1  18   set @seed=len(@split)  19     20   set @location=charindex(@split,@str)  21   while @location<>0 and @index>@next  22   begin  23     set @start=@location+@seed  24     set @location=charindex(@split,@str,@start)  25     set @next=@next+1  26   end  27   if @location =0 select @location =len(@str)+1  28  --这儿存在两种情况：、字符串不存在分隔符号2、字符串中存在分隔符号，跳出while循环后，@location为，那默认为字符串后边有一个分隔符号。 29     30   return substring(@str,@start,@location-@start)  31 end32 go

 1 create function Func_SplitStr(@SourceSql varchar(8000), @StrSePRate varchar(100))      2   returns @temp table(F1 varchar(100))      3   as        4   begin      5   declare @ch as varchar(100)      6   set @SourceSql=@SourceSql+@StrSeprate        7   while(@SourceSql<>'')      8   begin      9     set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)     10     insert @temp values(@ch)     11     set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')     12   end     13   return     14   end15 go

4

 1 create function Func_SplitStr(@str nvarchar(2000),@split nvarchar(2)) 2 returns @t table(AccountCodeID int ) 3 as  4 begin 5 declare @tmpAccountCodeID int,@getIndex int 6 set  @getIndex=charindex(',',@str) 7 while(@getIndex<>0)    8 begin 9     set @tmpAccountCodeID=convert(int,substring(@str,1,@getIndex-1))10     insert into @t(AccountCodeID) values (@tmpAccountCodeID)11     set @str=stuff(@str,1,@getIndex,'')12     set  @getIndex=charindex(',',@str)   13 end14 insert into @t(AccountCodeID) values (@str)15 return 16 end17 go