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

----------------------------------------------------------------

/**

• 版权： 石太祥 [ 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,',')

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)

 1<len(@splitstr) (@location<="" @int_return="0" charindex(@splitstr,@str)="0" if="" select="" while="">0)   
 2begin   
 3select @start = @location + 1   
 4select @location = charindex(@splitstr,@str,@start)   
 5select @next = @next + 1   
 6select @int_return = @next 
 7
 8end 
 9
10return @int_return   
11end 
12
13  
14GO   
15SET QUOTED_IDENTIFIER OFF   
16GO   
17SET ANSI_NULLS ON   
18GO 
19
20SET QUOTED_IDENTIFIER ON   
21GO   
22SET ANSI_NULLS ON   
23GO 
24
25  
26  
27－－ getstrofindex 输入一个未分割的字符串，舒服分割符号，舒服要取得的字符位置   
28－－ 返回 制定位置的字符串    
29CREATE function getstrofindex (@str varchar(8000),@splitstr varchar(4),@index int=0)   
30returns varchar(8000)   
31as   
32begin   
33declare @str_return varchar(8000)   
34declare @start int   
35declare @next int   
36declare @location int 
37
38select @start =1   
39select @next = 1 --如果习惯从0开始则select @next =0   
40select @location = charindex(@splitstr,@str,@start)   
41while (@location &lt;&gt;0 and @index &gt; @next ) 
42
43begin   
44select @start = @location +1   
45select @location = charindex(@splitstr,@str,@start)   
46select @next  =@next  +1   
47end   
48  
49if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后   
50select @str_return = substring(@str,@start,@location  -@start  )  \--@start  肯定是逗号之后的位置或者就是初始值1   
51if (@index &lt;&gt; @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。   
52  
53return @str_return   
54end 
55
56  
57GO   
58SET QUOTED_IDENTIFIER OFF   
59GO   
60SET ANSI_NULLS ON   
61GO</len(@splitstr)>