--测试 create table #t( a varchar(36) ) insert into #t select left(newid(),2) from sysobjects select * from #t select * from #t where a like '%[3,6,9]%' drop table #t
select * from 表 where 3 between --查询a=3 left(a,patindex('%[^0-9]%',a+'-')-1) and right(a,patindex('%[^0-9]%',reverse(a)+'-')-1)
--示例--示例数据 declare @t table(a varchar(10)) insert @t select '4' union all select '1-6' union all select '2+9' union all select '3'--查询 a=3 select * from @t where 3 between left(a,patindex('%[^0-9]%',a+'-')-1) and right(a,patindex('%[^0-9]%',reverse(a)+'-')-1)/*--测试结果a ---------- 1-6 2+9 3(所影响的行数为 3 行) --*/
declare @t table(a varchar(10)) insert @t select '4' union all select '1-6' union all select '2+9' union all select '3'select * from @t where left(a, case charindex( '-', replace(a,'+','-'), 1 ) when 0 then 8000 else charindex( '-', replace(a,'+','-'), 1 ) end - 1 ) <=3 and right(a, len(a)-charindex( '-', replace(a,'+','-'), 1 ) )>=3 结果 a ---------- 1-6 2+9 3
create table #t(
a varchar(36)
)
insert into #t select left(newid(),2) from sysobjects
select * from #t
select * from #t where a like '%[3,6,9]%'
drop table #t
where 3 between --查询a=3
left(a,patindex('%[^0-9]%',a+'-')-1)
and
right(a,patindex('%[^0-9]%',reverse(a)+'-')-1)
declare @t table(a varchar(10))
insert @t select '4'
union all select '1-6'
union all select '2+9'
union all select '3'--查询 a=3
select * from @t
where 3 between
left(a,patindex('%[^0-9]%',a+'-')-1)
and
right(a,patindex('%[^0-9]%',reverse(a)+'-')-1)/*--测试结果a
----------
1-6
2+9
3(所影响的行数为 3 行)
--*/
zjcxc(邹建)的可以,我给我朋友试试,一会来给分
insert @t select '4'
union all select '1-6'
union all select '2+9'
union all select '3'select * from @t where left(a, case charindex( '-', replace(a,'+','-'), 1 ) when 0 then 8000 else charindex( '-', replace(a,'+','-'), 1 ) end - 1 ) <=3 and right(a, len(a)-charindex( '-', replace(a,'+','-'), 1 ) )>=3
结果
a
----------
1-6
2+9
3