要求:查找某列(如result列)达到一定要求的记录 条件:result的数据必须是在'ABCDEFGH'字符窜中的一个或多个,如是ACD或BD或A等select * from tb where charindex(result , 'ABCDEFGH') > 0
select * from tb where charindex('A',result)>0 or charindex('ACD',result)>0
结帖方式:管理帖子->给分->输入密码->结帖.
--這樣? create table T(id int,result varchar(30)) insert into T select 1,'ABCD' insert into T select 2,'AC' insert into T select 3,'AX' insert into T select 4,'Y' insert into T select 5,'BD' insert into T select 6,'A' insert into T select 7,'ABCWF'GO create function fn_t(@str varchar(20)) returns @t table(col varchar(01)) as begin while len(@str)>0 begin insert into @t select left(@str,1) set @str=stuff(@str,1,1,'') end return endGO declare @s varchar(20) set @s='ABCDEFGH' select T.id,T.result from T, dbo.fn_t(@s) A where charindex(A.col,T.result)>0 group by T.id,T.result having count(*)=len(T.result) order by T.idGOdrop table T drop function fn_t
select result from goods where CHARINDEX('A',result)>0 or CHARINDEX('B',result)>0 or CHARINDEX('C',result)>0 or CHARINDEX('D',result)>0 or CHARINDEX('E',result)>0 or CHARINDEX('F',result)>0 or CHARINDEX('G',result)>0 or CHARINDEX('H',result)>0
playwarcraft 有可能是对的,其他的都答错了,哪有这么简单
/* 上述結果 id result ----------------- 1 ABCD 2 AC 5 BD 6 A*/
用PATINDEX估计也可以,大家快抢答
标准答案: select * from tb where patindex('%[ABCDEFGH]%',result)>0
--這樣試下 declare @str varchar(20) set @str='ABCDEFGH' select * from T where patindex('%['+@str+']%',result)>0 and patindex('%[^'+@str+']%',result)=0
To JiangHongTao 你的不行我看了结果,把包含其他字符的也出来,如AB44等,没什么效果
如果要查ACD? 那还查不查ADC?
恭喜 playwarcraft 答对了
这回绝对正确: select * from tb where patindex('%[^ABCDEFGH]%',result)=0
JiangHongTao 你的感觉差一点就是没考虑顺序!
如果是这样就用小梁子的.一个一个写.select * from tb where result = 'A' or result = 'B' or ... result = 'AB' ...
这回绝对正确: SQL codeselect * from tb where patindex('%[^ABCDEFGH]%',result)=0 ======================================= 呵呵,要是result='' 也會出來~~ 輕輕的我飄過~~~~~
用where like(% %)也可以呀,呵呵!
加顺序只好用自定义函数了 create function dbo.isin(@p,@r) as begin declare @i int,@j int,@k int if @r = '' return 1 --去除'' select @i=1,@k=0 while @i <=len(@r) begin set @j = charindex(@p,substring(@r,@i,1)) if @j =0 or @j <=@k return 1--如果不能重复,将等号去掉。 select @k = @j,@i=@i+1 end return 0 end go select * from tb where dbo.isin('ABCDEFGH',result) = 0
修正错误。 set @j = charindex(substring(@r,@i,1),@p) if @j =0 or @j <=@k return 1--如果能重复,将等号去掉。
from tb
where charindex('ACD',result)>0
条件:result的数据必须是在'ABCDEFGH'字符窜中的一个或多个,如是ACD或BD或A等select * from tb where charindex(result , 'ABCDEFGH') > 0
from tb
where charindex('A',result)>0 or charindex('ACD',result)>0
--這樣?
create table T(id int,result varchar(30))
insert into T select 1,'ABCD'
insert into T select 2,'AC'
insert into T select 3,'AX'
insert into T select 4,'Y'
insert into T select 5,'BD'
insert into T select 6,'A'
insert into T select 7,'ABCWF'GO
create function fn_t(@str varchar(20))
returns @t table(col varchar(01))
as
begin
while len(@str)>0
begin
insert into @t select left(@str,1)
set @str=stuff(@str,1,1,'')
end
return
endGO
declare @s varchar(20)
set @s='ABCDEFGH'
select T.id,T.result from T, dbo.fn_t(@s) A
where charindex(A.col,T.result)>0
group by T.id,T.result
having count(*)=len(T.result)
order by T.idGOdrop table T
drop function fn_t
where
CHARINDEX('A',result)>0 or CHARINDEX('B',result)>0 or CHARINDEX('C',result)>0 or
CHARINDEX('D',result)>0 or CHARINDEX('E',result)>0 or CHARINDEX('F',result)>0 or
CHARINDEX('G',result)>0 or CHARINDEX('H',result)>0
/*
上述結果
id result
-----------------
1 ABCD
2 AC
5 BD
6 A*/
select * from tb where patindex('%[ABCDEFGH]%',result)>0
順序有打亂的
所以簡單的charindex,或者patindex應該不行吧
--這樣試下
declare @str varchar(20)
set @str='ABCDEFGH'
select * from T
where patindex('%['+@str+']%',result)>0
and patindex('%[^'+@str+']%',result)=0
那还查不查ADC?
select * from tb where patindex('%[^ABCDEFGH]%',result)=0
=======================================
呵呵,要是result=''
也會出來~~
輕輕的我飄過~~~~~
create function dbo.isin(@p,@r)
as
begin
declare @i int,@j int,@k int
if @r = '' return 1 --去除''
select @i=1,@k=0
while @i <=len(@r)
begin
set @j = charindex(@p,substring(@r,@i,1))
if @j =0 or @j <=@k return 1--如果不能重复,将等号去掉。
select @k = @j,@i=@i+1
end
return 0
end
go
select * from tb where dbo.isin('ABCDEFGH',result) = 0
set @j = charindex(substring(@r,@i,1),@p)
if @j =0 or @j <=@k return 1--如果能重复,将等号去掉。
set @str='ABCDEFGH'
set @result = 'bcdefgha'if patindex('%[^'+@str+']%',@result) =0 print '1 '+@result else print '1 不符合'
if patindex('%['+@str+']%',@result)>0 and patindex('%[^'+@str+']%',@result) = 0
print '2 '+@result else print '2 不符合'
if dbo.isin(@str,@result) = 0 print '3 '+@result else print '3 不符合'
/*
1 bcdefgha
2 bcdefgha
3 不符合
*/
条件:result的数据必须是在'ABCDEFGH'字符窜中的一个或多个,如是ACD或BD或A等
不知道 这个问题解决了没有~刚出道的给个参考:
select result from tb where result in('ABCDEFGH')