createfunctionf_split(@cvarchar(2000),@splitvarchar(2)) returns@ttable(colvarchar(20)) as begin while(charindex(@split,@c)<>0) begin insert@t(col)values(substring(@c,1,charindex(@split,@c)-1)) set@c=stuff(@c,1,charindex(@split,@c),'') end insert@t(col)values(@c) return end go select*fromdbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',') dropfunctionf_split col -------------------- dfkd dfdkdf dfdkf dffjk
CREATEfunctionGet_StrArrayLength ( @strvarchar(1024),--要分割的字符串 @splitvarchar(10)--分隔符号 ) returnsint as begin declare@locationint declare@startint declare@lengthint set@str=ltrim(rtrim(@str)) set@location=charindex(@split,@str) set@length=1 while@location<>0 begin set@start=@location+1 set@location=charindex(@split,@str,@start) set@length=@length+1 end return@length end
调用示例:
selectdbo.Get_StrArrayLength('78,1,2,3',',')
返回值:4
三、按指定符号分割字符串,返回分割后指定索引的第几个元素,象数组一样方便
CREATEfunctionGet_StrArrayStrOfIndex ( @strvarchar(1024),--要分割的字符串 @splitvarchar(10),--分隔符号 @indexint--取第几个元素 ) returnsvarchar(1024) as begin declare@locationint declare@startint declare@nextint declare@seedint set@str=ltrim(rtrim(@str)) set@start=1 set@next=1 set@seed=len(@split) set@location=charindex(@split,@str) while@location<>0and@index>@next begin set@start=@location+@seed set@location=charindex(@split,@str,@start) set@next=@next+1 end if@location=0select@location=len(@str)+1 --这儿存在两种情况:1、字符串不存在分隔符号2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。 returnsubstring(@str,@start,@location-@start) end
调用示例:
selectdbo.Get_StrArrayStrOfIndex('8,9,4',',',2)
返回值:9
三、结合上边两个函数,象数组一样遍历字符串中的元素
declare@strvarchar(50) set@str='1,2,3,4,5' declare@nextint set@next=1 while@next<=dbo.Get_StrArrayLength(@str,',') begin printdbo.Get_StrArrayStrOfIndex(@str,',',@next) set@next=@next+1 end