本文主要是介绍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))ASBEGINDECLARE @CurrentIndex int;DECLARE @NextIndex int;DECLARE @ReturnText nvarchar(max);SELECT @CurrentIndex=1;WHILE(@CurrentIndex<=len(@SplitString))BEGINSELECT @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;ENDRETURN;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 intasbegindeclare @location intdeclare @start intdeclare @length intset @String=ltrim(rtrim(@String))set @location=charindex(@split,@String)set @length=1while @location<>0beginset @start=@location+1set @location=charindex(@split,@String,@start)set @length=@length+1endreturn @lengthend--调用示例
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)asbegindeclare @location intdeclare @start intdeclare @next intdeclare @seed intset @String=ltrim(rtrim(@String))set @start=1set @next=1set @seed=len(@split) set @location=charindex(@split,@String)while @location<>0 and @index>@nextbeginset @start=@location+@seedset @location=charindex(@split,@String,@start)set @next=@next+1endif @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。
转自：http://www.cnblogs.com/xiaofengfeng/archive/2012/06/01/2530930.html
这篇关于SQL自定义函数split分隔字符串的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
