SQL中自己创建函数 分割字符串
以下为引用的内容：
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,',')
以下为引用的内容：
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
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)<len(@splitstr)
select @int_return =0
if charindex(@splitstr,@str) = 0
select @int_return =0
while (@location<>0)
begin
select @start = @location + 1
以下为引用的内容：