程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> SQL中自己創建函數,分割字符串

SQL中自己創建函數,分割字符串

編輯:關於MYSQL數據庫

  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 =@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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved