mysql中sql切割字符串_SQL中自己创建函数分割字符串_MySQL

—————————————————————-

/**

* 版权： 石太祥 [ E.Alpha ] 所有 ;

*

* email: ealpha(AT)msn(DOT)com ;

* msn： ealpha(AT)msn(DOT)com ;

* QQ ： 9690501

*

* 所有转载请注明本信息！

*/

—————————————————————-

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getEPnum]’) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[getEPnum]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getstrcount]’) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[getstrcount]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getstrofindex]’) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[getstrofindex]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

－－－ 这个函数直接调用了另外的两个函数，可以先阅读下面提到的两个函数

CREATE function getEPnum (@str varchar(8000))

returns varchar(8000)

as

begin

declare @str_return varchar(8000)

declare @i int

declare @temp_i int

declare @onlineornot int

declare @findepnumok int

— 用来取得一个epnum，

— 规则：首先从chatid中取，如果有在线得，则取得最前面得在线得返回

— 如果全部不在线，则返回 ‘00000000’

select @findepnumok = 0

select @temp_i = 0

IF len(@str)<=0

begin

SELECT @str_return = ‘00000000’

end

else

begin

select @i = dbo.getstrcount(@str,’,’)=0

WHILE @temp_i

BEGIN

select @onlineornot = online from wwchat_user where epnum=dbo.getstrofindex(@str,’,’,@temp_i)

IF (@onlineornot=1)

begin

select @str_return =dbo.getstrofindex(@str,’,’,@temp_i)

select @findepnumok = 1 –找到epnum后置为1

BREAK

end

ELSE

begin

select @temp_i = @temp_i + 1

select @findepnumok = 0 –找不到epnum后置为1

end

END@i

if @findepnumok = 0

begin

SELECT @str_return = ‘00000000’

end

end

return @str_return

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

－－ getstrcount 输入一个没有分割的字符串，以及分割符

－－返回数组的个数

CREATE function getstrcount (@str varchar(8000),@splitstr varchar(100))

–returns varchar(8000)

returns int

as

begin

declare @int_return int

declare @start int

declare @next int

declare @location int

select @next = 0

select @location = 1

if len(@str)

select @int_return =0

if charindex(@splitstr,@str) = 0

select @int_return =0

while (@location0)

begin

select @start = @location + 1

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