Create functions in SQL, split strings

Create | function | string
----------------------------------------------------------------

/**

* Copyright: Shi Taixiang [E.alpha] all;

*

* Email:ealpha (AT) MSN (DOT) COM;

* Msn:ealpha (AT) MSN (DOT) COM;

* qq:9690501

*

* All reprint please indicate this information!

*/

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

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



---This function calls the other two functions directly, and you can read the two functions mentioned below first.
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

--To get a epnum,
--Rules: first from the Chatid, if there is online, then get the front must be returned online
--If all is not on the line, return ' 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--Find epnum after 1
Break
End
ELSE
Begin
Select @temp_i = @temp_i + 1
Select @findepnumok = 0--Epnum 1 not found
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 Enter a string that is not split, and the separator
--Returns the number of arrays



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 input an undivided string, comfortable to split the symbol, comfortable to get the character position
--Returns a string for the position drawn
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-Select @next if custom starts from 0 =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--if it is because there is no comma exit, the comma is considered after the string
Select @str_return = substring (@str, @start, @location-@start)--@start must be the position after the comma or the initial value 1
if (@index <> @next) Select @str_return = '--if the two are not equal, either because there are too few commas, or @index is less than the initial value of @next 1.

Return @str_return
End


Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go