分解字符串并查询相关数据

view plaincopy to clipboardprint?
问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。  
例如 @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 行） 
*/ 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/08/5159007.aspx