Several methods for implementing the SPLIT function in SQL

Example 1

The code is as follows:

Copy code

Create function f_split (@ SourceSql varchar (8000), @ StrSeprate varchar (10 )) Returns @ temp table (a varchar (100 )) -- Implement 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 affected rows is 4)

Example 2

The code is as follows:

Copy code

-- SQL Server Split function -- Author: zc_0101 -- Note: -- Multi-byte delimiters are 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) 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 @ long_str_Tmp ELSE BEGIN While charindex (@ split_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) + @ 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 END RETURN END

Example 3

Sql2000andSql2005 practical Split functions

 

The code is as follows:

Copy code

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 nvarchiar (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 (100) ') FROM @ x. nodes ('// items/item') AS x (item) RETURN END