SQL自定义函数split分隔字符串

一、F_Split：分割字符串拆分为数据表

Create FUNCTION [dbo].[F_Split] ( @SplitString nvarchar(max), --源字符串 @Separator nvarchar(10)=' ' --分隔符号，默认为空格 ) RETURNS @SplitStringsTable TABLE --输出的数据表

( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END --使用示例 select * FROm dbo.F_Split('111,b2222,323232,32d,e,323232f,g3222', ',')

结果为

id value

-------- ---------------------------------------

1 111

2 b2222

3 323232

4 32d

5 e

6 323232f

7 g3222

=========================================================================

二、F_SplitLength：获取分割后的字符数组的长度

Create function [dbo].[F_SplitLength] ( @String nvarchar(max), --要分割的字符串 @Split nvarchar(10) --分隔符号 ) returns int as begin declare @location int declare @start int declare @length int set @String=ltrim(rtrim(@String)) set @location=charindex(@split,@String) set @length=1 while @location<>0 begin set @start=@location+1 set @location=charindex(@split,@String,@start) set @length=@length+1 end return @length end --调用示例 select dbo.F_SplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

结果为7。

=========================================================================

三、F_SplitOfIndex：获取分割后特定索引的字符串

Create function [dbo].[F_SplitOfIndex] ( @String nvarchar(max), --要分割的字符串 @split nvarchar(10), --分隔符号 @index int --取第几个元素 ) returns nvarchar(1024) as begin declare @location int declare @start int declare @next int declare @seed int set @String=ltrim(rtrim(@String)) set @start=1 set @next=1 set @seed=len(@split) set @location=charindex(@split,@String) while @location<>0 and @index>@next begin set @start=@location+@seed set @location=charindex(@split,@String,@start) set @next=@next+1 end if @location =0 select @location =len(@String)+1 return substring(@String,@start,@location-@start) end --使用示例 select dbo.F_SplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

结果为323232。

转自：/xiaofengfeng/archive//06/01/2530930.html