WEB开发网
开发学院数据库MySQL SQL中自己创建函数,分割字符串 阅读

SQL中自己创建函数,分割字符串

 2008-10-20 11:11:39 来源:WEB开发网   
核心提示: select@location=charindex(@splitstr,@str,@start)select@next= @next + 1select@int_return = @next end return@int_returnendGOSET QUOTED_IDENTIFIER O

select @location = charindex(@splitstr,@str,@start) 

select @next = @next + 1

select @int_return = @next

end

return @int_return

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

-- getstrofindex 输入一个未分割的字符串,舒服分割符号,舒服要取得的字符位置

-- 返回 制定位置的字符串

CREATE  function getstrofindex (@str varchar(8000),@splitstr varchar(4),@index int=0) 

returns varchar(8000) 

as 

begin 

declare @str_return varchar(8000) 

declare @start int 

declare @next int 

declare @location int

select @start =1 

select @next = 1 --如果习惯从0开始则select @next =0 

select @location = charindex(@splitstr,@str,@start) 

while (@location <>0 and @index > @next )

begin 

select @start = @location +1 

select @location = charindex(@splitstr,@str,@start) 

select @next =@next +1 

end

if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出,则认为逗号在字符串后 

select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1 

if (@index <> @next ) select @str_return = '' --如果二者不相等,则是因为逗号太少,或者@index小于@next的初始值1。 

return @str_return 

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

上一页  1 2 3 

Tags:SQL 自己 创建

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接