select * from products where charindex(',8,31,',','+属性+',')>0
select * from products where charindex(',3,',','+属性+',')>0 and charindex(',6,',','+属性+',')>0
顶.连续的bill024(咖啡熊) 是对的 不连续的话,恐怕要先逗号的内容分开,查询
create table products(属性 varchar(20)) insert products select '1,3,5,6'--不连续的 select * from products where charindex(',3,',','+属性+',')>0 and charindex(',6,',','+属性+',')>0--连续的 select * from products where charindex(',3,5,',','+属性+',')>0drop table products
可以把A數組元素一個一個分解開,同B數組比較. exg:declare @a varchar(20),@b varchar(20),@i int,@temp int set @a='3,6' set @b='1,3,4,6' select @i=1,@temp=0while @i>0 begin select @i=charindex(',',@a) select @temp=@temp+case when charindex(','+left(@a,case when @i>0 then @i-1 else len(@a) end)+',',','+@b+',')>0 then 0 else 1 end select @a=stuff(@a,1,@i,'') endif @temp>0 print 'b不包含a' else print 'b包含a'
DECLARE @A NVARCHAR(50) DECLARE @B NVARCHAR(50) SET @A='1,3,5,6' SET @B='3,6'SELECT * FROM A WHERE @A LIKE REPLACE('%'+@B+'%',',','%') @A 可以是你的字段名字,@B是你要搜索的字符串
最好是写一个函数,可以比较后直接返回TRUE OR FALSE 的 可以吗?
--1=true,0=falseCreate function fn_test(@a varchar(50),@b varchar(50)) returns int AS begin declare @i int,@temp int select @i=1,@temp=0 while @i>0 begin select @i=charindex(',',@a) select @temp=@temp+case when charindex(','+left(@a,case when @i>0 then @i-1 else len(@a) end)+',',','+@b+',')>0 then 0 else 1 end select @a=stuff(@a,1,@i,'') end if @temp>0 set @temp=0 else set @temp=1 return @tempendGOselect dbo.fn_test('3,6','1,3,4,6') /* ----------- 1 */select dbo.fn_test('3,6,7','1,3,4,6') /* ----------- 0 */
lwl0606(寒泉)的思路很好,还可以延伸,这里要求数组有序,而且这样写结果不正确
wgzaaa() ( ) 信誉:100 Blog 加为好友 2007-06-08 10:14:31 得分: 0
我这样子搞已经可以了 select * from dbo.f_split('2,8,31',',') a where exists(select b.valueid from dbo.f_split('1,,,8,31',',') b where a.valueid=b.valueid and ltrim(b.valueid)<>'') and ltrim(a.valueid)<>'' group by a.valueid having count(a.valueid) >= dbo.getcpid('2,8,31',',')谢谢各位了我把这个下成一个函数返回逻辑值就可以了
不连续的话,恐怕要先逗号的内容分开,查询
create table products(属性 varchar(20))
insert products select '1,3,5,6'--不连续的
select * from products where charindex(',3,',','+属性+',')>0 and charindex(',6,',','+属性+',')>0--连续的
select * from products where charindex(',3,5,',','+属性+',')>0drop table products
exg:declare @a varchar(20),@b varchar(20),@i int,@temp int
set @a='3,6'
set @b='1,3,4,6'
select @i=1,@temp=0while @i>0
begin
select @i=charindex(',',@a)
select @temp=@temp+case when charindex(','+left(@a,case when @i>0 then @i-1 else len(@a) end)+',',','+@b+',')>0 then 0 else 1 end
select @a=stuff(@a,1,@i,'')
endif @temp>0
print 'b不包含a'
else
print 'b包含a'
DECLARE @B NVARCHAR(50)
SET @A='1,3,5,6'
SET @B='3,6'SELECT * FROM A
WHERE @A LIKE REPLACE('%'+@B+'%',',','%')
@A 可以是你的字段名字,@B是你要搜索的字符串
可以吗?
returns int
AS
begin
declare @i int,@temp int
select @i=1,@temp=0
while @i>0
begin
select @i=charindex(',',@a)
select @temp=@temp+case when charindex(','+left(@a,case when @i>0 then @i-1 else len(@a) end)+',',','+@b+',')>0 then 0 else 1 end
select @a=stuff(@a,1,@i,'')
end
if @temp>0
set @temp=0
else
set @temp=1
return @tempendGOselect dbo.fn_test('3,6','1,3,4,6')
/*
-----------
1
*/select dbo.fn_test('3,6,7','1,3,4,6')
/*
-----------
0
*/
lwl0606(寒泉)的思路很好,还可以延伸,这里要求数组有序,而且这样写结果不正确
-----------------------------------------------------------------------------------
以舉的例子來看
select REPLACE('%'+@B+'%',',','%')
結果為'%3%6%'
當然,@A如果為'1,3,4,6'是正好滿足,
跑開數組需要有序不談,
if @A='1,4,20,36'
就可以滿足.
所以,至少需要改進該思路,個人認為不可取
select * from dbo.f_split('2,8,31',',') a where exists(select b.valueid from dbo.f_split('1,,,8,31',',') b where a.valueid=b.valueid and ltrim(b.valueid)<>'') and ltrim(a.valueid)<>'' group by a.valueid having count(a.valueid) >= dbo.getcpid('2,8,31',',')谢谢各位了我把这个下成一个函数返回逻辑值就可以了