Summary of several methods for implementing the SPLIT function in SQL (required), sqlsplit

Summary of several methods for implementing the SPLIT function in SQL (required), sqlsplit

Example 1

The Code is as follows:

Create function f_split (@ SourceSql varchar (8000), @ StrSeprate varchar (10) returns @ temp table (a varchar (100) -- function for implementing the split function -- date: 2003-10-14as begindeclare @ I intset @ SourceSql = rtrim (ltrim (@ SourceSql) set @ I = charindex (@ StrSeprate, @ SourceSql) while @ I> = 1 begininsert @ temp values (left (@ SourceSql, @ i-1) set @ SourceSql = substring (@ SourceSql, @ I + 1, len (@ SourceSql) -@ I) set @ I = charindex (@ StrSeprate, @ SourceSql) endif @ SourceSql <> ''insert @ temp values (@ SourceSql) return endselect * from dbo. f_split ('1, 2, 3, 4 ',', ') a ------------------ 1234 (the number of affected rows is 4)

Example 2

The Code is as follows:

-- SQL Server Split function -- Author: zc_0101 -- Description: -- Multi-byte delimiter supported -- usage -- Select * FROM DBO. f_SQLSERVER_SPLIT ('20170', '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)-@ split_str_length) ELSESET @ long_str_Tmp = @ Long_strIF 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 @ long_str_Tmp elsebeginwhile 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, @ struct int SET @ long_str_Tmp_LEN = LEN (@ long_str_Tmp) SET @ struct = LEN (@ short_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 ENDRETURN END

Example 3

Sql2000andSql2005 practical Split Functions

The Code is as follows:

sql2000 CREATE FUNCTION [dbo].[splitstring_array] ( @string nvarchar(4000),@split char(1) ) RETURNS @array table( oneStr nvarchar(100) ) ASBEGINdeclare @v_code varchar(60) --zell 2006-05-26 --set @string = replace(@string,' ',@split) --set @string = replace(@string,'，',@split) while len(@string) > 0 beginif charindex(@split,@string,1) != 0 beginset @v_code = substring(@string,1,charindex(@split,@string,1)-1) set @string = substring(@string,charindex(@split,@string,1)+1,len(@string)) endelse if charindex(@split,@string,1) = 0 beginset @v_code = @string set @string = ''endinsert into @array(onestr) values(@v_code) endRETURNENDsql2005 CREATE function [dbo].[func_splitid] (@str varchar(max),@split varchar(10)) RETURNS @t Table (c1 nvarchar(100)) ASBEGINDECLARE @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(100)') FROM @x.nodes('//items/item') AS x(item) RETURN END 

The summary of several methods for implementing the SPLIT function in the preceding SQL statement (this article is required) is all the content shared by the editor. I hope you can give us a reference and support the help house.