资源描述：
《sql自定义函数split分隔字符串》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、SQL自定义函数split分隔字符串一、F_Split：分割字符串拆分为数据表CreateFUNCTION[dbo].[F_Split](@SplitStringnvarchar(max),--源字符串@Separatornvarchar(10)=''--分隔符号，默认为空格)RETURNS@SplitStringsTableTABLE--输出的数据表([id]intidentity(1,1),[value]nvarchar(max))ASBEGINDECLARE@CurrentIndexint;DECLARE@NextIndexint;DECLARE@ReturnTextnvarchar(m
2、ax);SELECT@CurrentIndex=1;WHILE(@CurrentIndex<=len(@SplitString))BEGINSELECT@NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);IF(@NextIndex=0OR@NextIndexISNULL)SELECT@NextIndex=len(@SplitString)+1;SELECT@ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);INSERTINTO@S
3、plitStringsTable([value])VALUES(@ReturnText);SELECT@CurrentIndex=@NextIndex+1;ENDRETURN;END--使用示例select*FROmdbo.F_Split('111,b2222,323232,32d,e,323232f,g3222',',')结果为id value-----------------------------------------------1 1112 b22223 3232324 32d5
4、 e6 323232f7 g3222====================================二、F_SplitLength：获取分割后的字符数组的长度Createfunction[dbo].[F_SplitLength](@Stringnvarchar(max),--要分割的字符串@Splitnvarchar(10)--分隔符号)returnsintasbegindeclare@locationintdeclare@startintdeclare@lengthintset@String=ltrim(rtrim(@String))set@loc
5、ation=charindex(@split,@String)set@length=1while@location<>0beginset@start=@location+1set@location=charindex(@split,@String,@start)set@length=@length+1endreturn@lengthend--调用示例selectdbo.F_SplitLength('111,b2222,323232,32d,e,323232f,g3222',',')结果为7。 ====================================三、F_SplitOfInde
6、x：获取分割后特定索引的字符串Createfunction[dbo].[F_SplitOfIndex](@Stringnvarchar(max),--要分割的字符串@splitnvarchar(10),--分隔符号@indexint--取第几个元素)returnsnvarchar(1024)asbegindeclare@locationintdeclare@startintdeclare@nextintdeclare@seedintset@String=ltrim(rtrim(@String))set@start=1set@next=1set@seed=len(@split)set@locat
7、ion=charindex(@split,@String)while@location<>0and@index>@nextbeginset@start=@location+@seedset@location=charindex(@split,@String,@start)set@next=@next+1endif@location=0select@location=len(@String)+1re
