Several methods of implementing Split function in SQL

Example 1

Code to copy code as follows
Create function F_split (@SourceSql varchar (8000), @StrSeprate varchar (10))
Returns @temp table (a varchar (100))
--function to realize split function
--date:2003-10-14
As
Begin
DECLARE @i int
Set @SourceSql =rtrim (LTrim (@SourceSql))
Set @i=charindex (@StrSeprate, @SourceSql)
While @i>=1
Begin
Insert @temp values (left (@SourceSql, @i-1))
Set @SourceSql =substring (@SourceSql, @i+1,len (@SourceSql) [email protected]
Set @i=charindex (@StrSeprate, @SourceSql)
End
If @SourceSql <> '
Insert @temp VALUES (@SourceSql)
Return
End

SELECT * from Dbo.f_split (' 1,2,3,4 ', ', ')

A
--------------------
1
2
3
4

(The number of rows affected is 4 rows)

Example 2

Code to copy code as follows
--sql Server Split function
--author:zc_0101
--Description:
--Support for multi-byte delimiters
--How to use
--select * from DBO. F_sqlserver_split (' 1203401230105045 ', ' 0 ')
--select * from DBO. F_sqlserver_split (' abc1234a12348991234 ', ' 1234 ')
--select * from DBO. F_sqlserver_split (' ABC ', ', ')

CREATE FUNCTION f_sqlserver_split (@Long_str varchar (8000), @split_str varchar (100))
RETURNS @tmp TABLE (
ID InT IDENTITY PRIMARY KEY,
SHORT_STR varchar (8000)
)
As
BEGIN
DECLARE @long_str_Tmp varchar (8000), @short_str varchar (8000), @split_str_length int
SET @split_str_length = LEN (@split_str)
IF CHARINDEX (@split_str, @Long_str) =1
SET @long_str_Tmp =substring (@Long_str, @split_str_length +1,len (@Long_str) [email protected]_str_length)
ELSE
SET @[email Protected]_str
IF CHARINDEX (REVERSE (@split_str), REVERSE (@long_str_Tmp)) >1
SET @[email Protected][email Protected]_str
ELSE
SET @[email protected]_str_tmp
IF CHARINDEX (@split_str, @long_str_Tmp) =0
Insert into @tmp Select @long_str_Tmp
ELSE
BEGIN
While CHARINDEX (@spl (www.111cn.net) it_str, @long_str_Tmp) >0
BEGIN
SET @short_str =substring (@long_str_Tmp, 1,charindex (@split_str, @long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN int, @split_str_Position_END int
SET @long_str_Tmp_LEN = LEN (@long_str_Tmp)
SET @split_str_Position_END = LEN (@short_str) [Email protected]_str_length
SET @long_str_Tmp =reverse (SUBSTRING (REVERSE (@long_str_Tmp), 1,@[email protected]_str_position_end))
IF @short_str <> ' Insert into @tmp select @short_str
END
END
RETURN
END

Example 3

sql2000andsql2005 Practical Split function

Code to copy code as follows
sql2000
CREATE FUNCTION [dbo]. [Splitstring_array]
(
@string nvarchar (4000), @split char (1)
)

RETURNS @array Table
(
Onestr nvarchar (100)
)

As

BEGIN
DECLARE @v_code varchar (60)

--zell 2006-05-26
--set @string = replace (@string, ", @split)
--set @string = replace (@string, ', ', @split)

While Len (@string) > 0
Begin
If CHARINDEX (@split, @string, 1)! = 0
Begin
Set @v_code = substring (@string, 1,charindex (@split, @string, 1)-1)
Set @string = substring (@string, charindex (@split, @string, 1) +1,len (@string))
End
else if CHARINDEX (@split, @string, 1) = 0
Begin
Set @v_code = @string
Set @string = ' '
End
Insert into @array (ONESTR) VALUES (@v_code)
End
RETURN
END

sql2005
CREATE function [dbo]. [Func_splitid]
(@str varchar (max), @split varchar (10))
RETURNS @t Table (C1 nvarchar (100))
As
BEGIN
DECLARE @x XML
SET @x = CONVERT (XML, ' <items><item id= ' + REPLACE (@str, @split, ' "/><item id=" ') + '/></items> ' )
INSERT into @t SELECT x.item.value (' @id [1] ', ' nvarchar (+) ') from @x.nodes ('//items/item ') as x (item)
RETURN
END
From:http://www.111cn.net/database/mssqlserver/53207.htm

Several methods of implementing Split function in SQL