说明：通过使用函数等方法分解字符串查询相关数据。
问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
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
-----------------------------
--如果仅仅是一个,如@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 1,2,3,4,5,6,7,8,9,10,11,12
（所影响的行数为 1 行）
*/
-----------------------------
--如果包含两个,如@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 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
4 2,6
（所影响的行数为 3 行）
*/
-------------------------------------------
--如果包含三个或四个，用PARSENAME函数来处理.
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 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
（所影响的行数为 5 行）
*/
---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
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
--调用
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 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
（所影响的行数为 5 行）
*/
------------------------------------------
--使用动态SQL的语句。
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 a.* FROM tb a , (' + @sql + ') b WHERE charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*
ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
2 2,3
3 3,7,8,9
4 2,6
5 4,5
（所影响的行数为 5 行）
*/
//sql/1211