问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @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
*/-----------------------------createtabletb (IDint, TypeIDvarchar(30))insertintotbvalues(1,'1,2,3,4,5,6,7,8,9,10,11,12')insertintotbvalues(2,'2,3')insertintotbvalues(3,'3,7,8,9')insertintotbvalues(4,'2,6')insertintotbvalues(5,'4,5')insertintotbvalues(6,'6,7')go-------------------------------如果仅仅是一个,如@str = '1'.declare@strasvarchar(30)set@str='1'select*fromtbwherecharindex(','+@str+',',','+TypeID+',')>0select*fromtbwhere','+TypeID+','like'%,'+@str+',%'/*ID TypeID
----------- ------------------------------
1 1,2,3,4,5,6,7,8,9,10,11,12
(所影响的行数为 1 行)*/-------------------------------如果包含两个,如@str = '1,2'.declare@strasvarchar(30)set@str='1,2'select*fromtbwherecharindex(','+left(@str,charindex(',',@str)-1)+',',','+typeid+',')>0orcharindex(','+substring(@str,charindex(',',@str)+1,len(@str))+',',','+typeid+',')>0select*fromtbwhere','+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@strasvarchar(30)set@str='1,2,3,4'select*fromtbwherecharindex(','+parsename(replace(@str,',','.') ,4)+',',','+typeid+',')>0orcharindex(','+parsename(replace(@str,',','.') ,3)+',',','+typeid+',')>0orcharindex(','+parsename(replace(@str,',','.') ,2)+',',','+typeid+',')>0orcharindex(','+parsename(replace(@str,',','.') ,1)+',',','+typeid+',')>0select*fromtbwhere','+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函数.
功能:实现字符串分隔功能的函数*/createfunctiondbo.fn_split(@inputstrvarchar(8000),@sepratorvarchar(10))returns@temptable(avarchar(200))asbegindeclare@iintset@inputstr=rtrim(ltrim(@inputstr))set@i=charindex(@seprator,@inputstr)while@i>=1begininsert@tempvalues(left(@inputstr,@i-1))set@inputstr=substring(@inputstr,@i+1,len(@inputstr)-@i)set@i=charindex(@seprator,@inputstr)endif@inputstr<>'\'insert@tempvalues(@inputstr)returnendgo--调用declare@strasvarchar(30)set@str='1,2,3,4,5'selectdistinctm.*fromtb m,
(select*fromdbo.fn_split(@str,',')) nwherecharindex(','+n.a+',',','+m.typeid+',')>0droptabletbdropfunctiondbo.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@strvarchar(200)declare@sqlasvarchar(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 行)*/