The SQL Server String splitting (split) method is used to collect and learn carefully)

 

SQL Server String splitting (split) method summary

-- Method 0: dynamic SQL
Declare @ s varchar (100), @ SQL varchar (1000)
Set @ s = '1, 2, 3, 4, 5, 6, 7, 8, 9, 10'
Set @ SQL = 'select Col = ''' + Replace (@ s, ',', ''' Union all select ''') + ''''
Print @ SQL
Exec (@ SQL)

 

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go
-- Method 1: Circular Truncation
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (COL varchar (100 ))
As
Begin
Declare @ splitlen int
Set @ splitlen = Len (@ split + 'A')-2
While charindex (@ split, @ s)> 0
Begin
Insert @ Re values (left (@ s, charindex (@ split, @ s)-1 ))
Set @ s = stuff (@ s, 1, charindex (@ split, @ s) + @ splitlen ,'')
End
Insert @ Re values (@ s)
Return
End
Go

 

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go
-- Method 2: Use the temporary partitioning auxiliary Table Method
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (COL varchar (100 ))
As
Begin
-- Create a secondary table for splitting (only table variables can be operated in user-defined functions)
Declare @ t table (ID int identity, B bit)
Insert @ T (B) Select top 8000 0 from syscolumns A, syscolumns B

Insert @ Re select substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID)
From @ t
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID
Return
End
Go

 

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tb_splitstr] ') and objectproperty (ID, n' isusertable') = 1)
Drop table [DBO]. [tb_splitstr]
Go
-- Method 3: Permanent partitioning of auxiliary tables
-- String splitting auxiliary table
Select top 8000 id = identity (INT,) into DBO. tb_splitstr
From syscolumns A, syscolumns B
Go
-- String splitting Handler
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns table
As
Return (
Select Col = cast (substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID) as varchar (100 ))
From tb_splitstr
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID)
Go

 

-- Method 4: use SQL server2005 outer apply

Create Function [DBO]. [ufn_splitstringtotable]
(
@ STR varchar (max ),
@ Split varchar (10)
)
Returns table
As
Return
(Select B. ID
From (select [value] = convert (XML, '<v>' + Replace (@ STR, @ split, '</V> <v> ')
+ '</V> ')
)
Outer apply (select id = n. v. Value ('.', 'varchar (100 )')
From a. [value]. nodes ('/V') N (v)
) B
)

 

Note:

Method 4 can be run only in SQL server2005