Splits string T-SQL based on the delimiter

There are three methods to split strings Based on delimiters:

1. Use the Union all Method

Code

 -- Use the Union all Method
-- The idea is to assemble the Union into a SELECT statement [select 'zhang san' Union all select 'Li si' Union all select 'wang wu']
Declare @ STR nvarchar (max );
Set @ STR = 'zhang San, Li Si, Wang wu ';
Set @ STR = 'select' + ''' + Replace (@ STR, ',', ''' Union all select ''') + '''';
Print @ STR;
Exec (@ Str );

 

2. Use a temporary table

 

Code

 -- Use temporary tables
-- The train of thought is to piece it together into a string of [, Zhang San, Li Si, Wang Wu,], find the position of each [,] character, and then extract it through the substring function in sequence
Declare @ STR nvarchar (max );
Set @ STR = n', '+ N' Zhang San, Li Si, Wang wu' + N ',';

-- Create a temporary table
Select top (50) Identity (INT, 1, 1) as col1
Into # demo1
From SYS. Objects o, SYS. Objects O2

-- Create the character [,] position sequence table # demo2
Select row_number () over (order by col1 ASC) as Ident,
Col1 into # demo2 from # demo1
Where col1 = charindex (',', @ STR, col1 );

-- Truncate a string based on [,] characters
Select substring (@ STR, d3.col1 + 1, d2.col1-d3.col1-1) from # demo2 D2
Inner join # demo2 D3
On d2.ident = d3.ident + 1;
Drop table # demo1, # demo2;
Go

 

3. Use the CTE method to solve the problem. The idea is the same as that of using a temporary table.

 

Code

 -- Use the preceding tsql statement together with CTE
Declare @ STR nvarchar (max );
Set @ STR = n', '+ N' Zhang San, Li Si, Wang wu' + N ','
; With # demo1
(
Select top (50) row_number () over (order by [object_id] ASC) as col1
From SYS. Objects
Order by col1 ASC
),
# Demo2
(
Select row_number () over (order by col1 ASC) as Ident,
Col1 from # demo1
Where col1 = charindex (',', @ STR, col1)
)
Select substring (@ STR, d3.col1 + 1, d2.col1-d3.col1-1) from # demo2 D2
Inner join # demo2 D3
On d2.ident = d3.ident + 1;

 

Summary: String splitting is nothing more than this: the temporary table method, dynamic tsql, the cycle method, the cursor method, and the common expression (CTE) method always have an optimal

 

 

2. Use auxiliary tables and temporary tables

 

-- Use auxiliary table, temporary table method -- use master .. spt_values uses the secondary number column from-2048if object_id ('tempdb .. # t') is not null drop table tempdb .. # T; Create Table # T (COL int identity (), number INT); declare @ STR nvarchar (max); Set @ STR = 'zhangsan, Lisi, Wang wu '; set @ STR = ',' + @ STR + ','; -- creates a temporary table. The table variable cannot use the alias insert into # T (number) Select Sv. number from [Master]. DBO. spt_values SV where type = 'p' and number = charindex (',', @ STR, Sv. number); select substring (@ STR, number + 1, name_length-1) as col from (select t1.number, t2.number-t1.number as name_length from # T T1 inner join # T t2on t1.col + 1 = t2.col) as TMP

3. Use the circular truncation method (online excerpt)

-- Split the string-create function dbo using the circular truncation method. f_splitstr (@ s varchar (8000), -- string to be split @ split varchar (10) -- Data Separator) returns @ Re table (COL varchar (100 )) asbegindeclare @ splitlen int -- Take the length of the separator. Add a character after the separator to avoid ending with a space, set @ splitlen = Len (@ split + 'A')-2 cannot be obtained. -- if the string to be split contains a Data Separator, each data item while charindex (@ split, @ s)> 0begin is retrieved cyclically -- the data item before the first data separator is inserted @ Re values (left (@ s, charindex (@ split, @ s)-1) -- remove the first data item and Data Separator from the string to be split. Set @ s = stuff (@ s, 1, charindex (@ split, @ s) + @ splitlen, '') end -- save the last data item (there is no data separator behind the last data item, so it will not be processed in the previous loop) insert @ Re values (@ s) returnendgo