《SQL自定义函数split分隔字符串》由会员分享，可在线阅读，更多相关《SQL自定义函数split分隔字符串（5页珍藏版）》请在金锄头文库上搜索。
1、文档供参考，可复制、编制，期待您的好评与关注！ 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 NextInd
2、ex 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,Ne
3、xtIndex-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 1112 b22223 3232324 32d5 e6 323232f7 g3222=二、F_SplitLength：获取分割后的字符数组的长度Create funct
4、ion 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 location0 begin set start=location+1 set location=ch
5、arindex(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 nvarc
6、har(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 location0 and indexnext 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。 /
