Break down strings and query related data

/*
Title: breaking down strings and querying related data
Author: AI xinjue Luo. Xin Hua)
Time: 2008-03-18
Location: Shenzhen, Guangdong Province
Note: You can use functions and other methods to break down strings to query relevant data.

Problem: Search for related data in the database by breaking down a string separated by a certain symbol.
For example, @ STR = '1, 2, 3 '. query the following table to obtain records 1, 4, 5, and 6.
Id typeid
1
2, 3
3, 7, 8, 9
4, 2, 6
5, 4, 5
6, 6, 7
*/
-----------------------------
Create Table Tb (ID int, typeid varchar (30 ))
Insert into TB values (1, '1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ')
Insert into TB values (2, '2, 3 ')
Insert into TB values (3, '3, 7, 8, 9 ')
Insert into TB values (4, '2, 6 ')
Insert into TB values (5, '4, 5 ')
Insert into TB values (6, '6, 7 ')
Go
-----------------------------
-- For example, @ STR = '1 '.
Declare @ STR as varchar (30)
Set @ STR = '1'
Select * from TB where charindex (',' + @ STR + ',' + typeid + ',')> 0
Select * from TB where ',' + typeid + ', 'like' %,' + @ STR + ', %'
/*
Id typeid
-----------------------------------------
1
(The number of affected rows is 1)
*/

-----------------------------
-- If there are two, for example, @ STR = '1, 2 '.
Declare @ STR as varchar (30)
Set @ STR = '1, 2'
Select * from TB where charindex (',' + Left (@ STR, charindex (',', @ Str)-1) + ',',', '+ typeid +', ')> 0 or
Charindex (',' + substring (@ STR, charindex (',', @ Str) + 1, Len (@ Str) + ',',', '+ typeid +', ')> 0
Select * from TB where ',' + typeid + ', 'like' %,' + Left (@ STR, charindex (',', @ Str)-1) + ', % 'or
',' + Typeid + ', 'like' %,' + substring (@ STR, charindex (',', @ Str) + 1, Len (@ Str )) + ', %'
/*
Id typeid
-----------------------------------------
1
2, 3
4, 2, 6
(The number of affected rows is 3)
*/

-------------------------------------------
-- If it contains three or four, use the parsename function for processing.
Declare @ STR as varchar (30)
Set @ STR = '1, 2, 3, 4'
Select * from TB where
Charindex (',' + parsename (replace (@ STR ,',','. '), 4) +', '+ typeid +', ')> 0 or
Charindex (',' + parsename (replace (@ STR ,',','. '), 3) +', '+ typeid +', ')> 0 or
Charindex (',' + parsename (replace (@ STR ,',','. '), 2) +', '+ typeid +', ')> 0 or
Charindex (',' + parsename (replace (@ STR ,',','. '), 1) +', '+ typeid +', ')> 0
Select * from TB where
',' + Typeid + ', 'like' %,' + parsename (replace (@ STR, '.'), 4) + ', %' or
',' + Typeid + ', 'like' %,' + parsename (replace (@ STR, '.'), 3) + ', %' or
',' + Typeid + ', 'like' %,' + parsename (replace (@ STR, '.'), 2) + ', %' or
',' + Typeid + ', 'like' %,' + parsename (replace (@ STR, '.'), 1) + ', %'
/*
Id typeid
-----------------------------------------
1
2, 3
3, 7, 8, 9
4, 2, 6
5, 4, 5
(The number of affected rows is 5)
*/

---------------------------------------
-- If there are more than four, you can only use functions or dynamic SQL statements to break down and query data.
/*
Name: fn_split function.
Function: a function that separates strings.
*/
Create Function DBO. fn_split (@ inputstr varchar (8000), @ seprator varchar (10 ))
Returns @ temp table (A varchar (200 ))
As
Begin
Declare @ I int
Set @ inputstr = rtrim (ltrim (@ inputstr ))
Set @ I = charindex (@ seprator, @ inputstr)
While @ I> = 1
Begin
Insert @ temp values (left (@ inputstr, @ I-1 ))
Set @ inputstr = substring (@ inputstr, @ I + 1, Len (@ inputstr)-@ I)
Set @ I = charindex (@ seprator, @ inputstr)
End
If @ inputstr <> '\'
Insert @ temp values (@ inputstr)
Return
End
Go

-- Call
Declare @ STR as varchar (30)
Set @ STR = '1, 2, 3, 4, 5'

Select distinct M. * from TB m,
(Select * From DBO. fn_split (@ STR, ',') N
Where charindex (',' + n. A + ',' + M. typeid + ',')> 0

Drop table TB
Drop function DBO. fn_split

/*
Id typeid
-----------------------------------------
1
2, 3
3, 7, 8, 9
4, 2, 6
5, 4, 5
(The number of affected rows is 5)
*/

------------------------------------------
-- Use dynamic SQL statements.
Declare @ STR varchar (200)
Declare @ SQL as varchar (1000)
Set @ STR = '1, 2, 3, 4, 5'
Set @ SQL = 'select' + Replace (@ STR, ',', ''' as ID Union all select ''')
Set @ SQL = @ SQL + ''''
Set @ SQL = 'select distinct. * from tb a, ('+ @ SQL +') B where charindex ('+ ''', ''+ B. ID + '+ ''', ''' +', '+ ''', ''+. typeid + '+ ''', ''' +')> 0'
Exec (@ SQL)
/*
Id typeid
-----------------------------------------
1
2, 3
3, 7, 8, 9
4, 2, 6
5, 4, 5
(The number of affected rows is 5)
*/