---------------------------------------------------------------- PHP程序员站
/** www phperz com
* 版权: 石太祥 [ E.Alpha ] 所有 ; PHP程序员站--PHP程序员之家
* PHP程序员站
* email: ealpha(AT)msn(DOT)com ; PHP程序员站--PHP程序员之家
* msn: ealpha(AT)msn(DOT)com ; www~phperz~.com
* QQ : 9690501 phperz.com
* phperz.com
* 所有转载请注明本信息! PHP程序员站
*/ www~phperz~.com
----------------------------------------------------------------
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 phperz.com
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 www phperz com
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 phperz.com
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO PHP程序员站--PHP程序员之家
--- 这个函数直接调用了另外的两个函数,可以先阅读下面提到的两个函数 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 www~phperz~.com
-- 用来取得一个epnum, -- 规则:首先从chatid中取,如果有在线得,则取得最前面得在线得返回 -- 如果全部不在线,则返回 ‘00000000’ www~phperz~.com
select @findepnumok = 0 select @temp_i = 0
IF len(@str)<=0 begin SELECT @str_return = '00000000' end else begin select @i = dbo.getstrcount(@str,',') PHP程序员站--PHP程序员之家
WHILE @temp_i<@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 phperz.com
if @findepnumok = 0 begin SELECT @str_return = '00000000' end end return @str_return end www~phperz~.com
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO phperz.com
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO www phperz com
-- getstrcount 输入一个没有分割的字符串,以及分割符 --返回数组的个数
www~phperz~.com
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 PHP程序员站--PHP程序员之家
declare @location int
select @next = 0 select @location = 1 www.phperz.com
if len(@str)<len(@splitstr) select @int_return =0 if charindex(@splitstr,@str) = 0 select @int_return =0 PHP程序员站
while (@location<>0) begin select @start = @location + 1 select @location = charindex(@splitstr,@str,@start) select @next = @next + 1 select @int_return = @next www.phperz.com
end PHP程序员站
return @int_return end www phperz com
phperz.com
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
phperz.com
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO PHP程序员站--PHP程序员之家
-- 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
www.phperz.com
select @start =1 select @next = 1 --如果习惯从0开始则select @next =0 select @location = charindex(@splitstr,@str,@start) while (@location <>0 and @index > @next ) PHP程序员站
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 PHP程序员站
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
www.phperz.com
|