Summary of several methods of implementing Split function in SQL (must see article) _mysql

Example 1

The code is as follows

Create function F_split (@SourceSql varchar (8000), @StrSeprate varchar)
returns @temp table (a varchar (100))
--function of the 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)-@i)
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

The code is as follows

--sql Server Split Function--author:zc_0101--Description:--support delimiter multibyte--use method--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 (80 , @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)-@split_str_length) ELSE SET @ long_str_tmp= @Long_str IF CHARINDEX (REVERSE (@split_str), REVERSE (@long_str_Tmp)) >1 SET @long_str_Tmp = @long_str_ tmp+ @split_str ELSE SET @long_str_Tmp = @long_str_Tmp IF CHARINDEX (@split_str, @long_str_Tmp) =0 Insert into @tmp select @l Ong_str_tmp ELSE BEGIN while CHARINDEX (@spl (www.jb51.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 (@sh ORT_STR) + @split_str_length SET @long_str_Tmp =reverse (SUBSTRING (REVERSE (@long_str_Tmp), 1, @long_str_Tmp_LEN-@split _str_position_end) IF @short_str <> ' Insert into @tmp select @short_str End

Example 3

sql2000andsql2005 Practical Split function

The code is as follows

sql2000 CREATE FUNCTION [dbo]. 
[Splitstring_array] 

(@string nvarchar (4000), @split char (1))  RETURNS @array table (onestr nvarchar) as BEGIN declare @v_code varchar (MB)--zell 2006-05-26--set @string = Replace (@string, ', @split)--set @string = replace (@string, ', ', @split) while Len (@string) > 0 BEGIN if Charinde X (@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 = @str ing set @string = ' End insert to @array (ONESTR) VALUES (@v_code) end return sql2005 CREATE function [dbo]. 
[Func_splitid] 
(@str varchar (max), @split varchar (10)) RETURNS @t Table (C1 nvarchar) 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(m) ') from @x.nodes ('//items/item ') as x (item) 
 

The above in this SQL to implement the split function of several methods summarized (must read) is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud habitat community.