SQL 中自己創建函數，分割字符串
2024-07-21 02:06:53
供稿：網友
----------------------------------------------------------------
/**
* 版權： 石太祥 [ 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)<len(@splitstr)
select @int_return =0
if charindex(@splitstr,@str) = 0
select @int_return =0
while (@location<>0)
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 [email protected] +1
end
if @location =0 select @location =len(@str)+1 --如果是因為沒有逗號退出，則認為逗號在字符串后
select @str_return = substring(@str,@start,@location [email protected]) [email protected]肯定是逗號之后的位置或者就是初始值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
