java 字符串查询添加数据_分解字符串并查询相关数据

问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。

例如 @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 行)*/