Create a function to split the string in SQL _ MySQL

Create a function split string in SQL ----------------------------------------------------------------

/**

* Copyright: Owned by Shi Taixiang [E. Alpha;

*

* Email: ealpha (AT) msn (DOT) com;

* Msn: ealpha (AT) msn (DOT) com;

* QQ: 9690501

*

* Indicate this information for all reposts!

*/

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

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 directly calls the other two functions. read the two functions mentioned below.
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

-- Used to obtain an epnum,
-- Rule: first retrieve from the chatid. if the chatid is online, obtain the first online result.
-- If all data is not online, '20140901' is returned'

Select @ findepnumok = 0
Select @ temp_ I = 0

IF len (@ str) <= 0
Begin
SELECT @ str_return = '20140901'
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 -- locate epnum and set it to 1
BREAK
End
ELSE
Begin
Select @ temp_ I = @ temp_ I + 1
Select @ findepnumok = 0 -- 1 after epnum is not found
End
END

If @ findepnumok = 0
Begin
SELECT @ str_return = '20140901'
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 an unsegmented string and delimiter.
-- 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) 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: enter an unsegmented string, which is a comfortable separator and a comfortable character position to be obtained.
-- Returns the string at the specified position.
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 -- if you are used to starting from 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 -- if there is no comma to 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 be the initial value 1.
If (@ index <> @ next) select @ str_return = ''-- if the two are not equal, it is because there are too few commas, or @ index is less than the initial value of @ next.

Return @ str_return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO