正则表达式

[delectation]

表中有一列,形式如下

qq-001-100
qq-001-100R
bb-123-234
bb-256-100R0
bb-256-100R1
...
bb-256-100R9

cc-678-123B
....

在查询时排除以R,B,R0~R9结尾的数据.我写了一个[^RB],可是不能排除以R0~R9这样结尾的.

[JL99000]

if object_id('tb') is not null
drop table tb
go
create table tb(mm varchar(100))
insert tb
select 'qq-001-100' union all
select 'qq-001-100R' union all
select 'bb-123-234' union all
select 'bb-256-100R0' union all
select 'bb-256-100R1' union all
select 'bb-256-100R9' union all
select 'cc-678-123B'

select * from tb
where right(mm,1)<>'R' and right(mm,1)<>'B' and right(mm,2) not like '%R[0-9]'

接分

[fcuandy]

再次声明,这不是正则,这是简单的通配符

declare @t table(val varchar(50))
insert into @t select 'qq-001-100'
insert into @t select 'qq-001-100R'
insert into @t select 'bb-123-234'
insert into @t select 'bb-256-100R0'
insert into @t select 'bb-256-100R1'
insert into @t select 'bb-256-100R9'
insert into @t select 'cc-678-123B'

select * from @t where val like '%[^r][^rb]'

[delectation]

to fcuandy
请解释一下.谢谢

[fcuandy]

其实我写的这个有条件限制的.

排除 你的数据中有 rx,rz,rv,ra 结尾的这样的数据的可能.

也即你如果以 r? 结尾只有 r0--r9 这样是可以的.

[pt1314917]

select * from @t where 
patindex('%[^RB]',val)>0
and
patindex('%[^R0]',val)>0
and
patindex('%[^R9]',val)>0

[popeye627]

select * from
(
select 'qq-001-100' as a union
select 'qq-001-100R'  union
select 'bb-123-234'  union
select 'bb-256-100R0'  union
select 'bb-256-100RT1'  union
select 'bb-256-100R9'  union
select 'cc-678-123B'  union
select 'bb-256-100B3'  union
select 'bb-256-100R4'  union
select 'bb-256-100R6'  
) t
where right(a,1) not in ('R','B')
and a not like '%_R[0-9]'

-------------------------------
a
-------------
bb-123-234
bb-256-100B3
bb-256-100RT1
qq-001-100

(4 個資料列受到影響)

[pt1314917]

declare @t table(val varchar(50))
insert into @t select 'qq-001-100'
insert into @t select 'qq-001-100R'
insert into @t select 'bb-123-234'
insert into @t select 'bb-256-100R0'
insert into @t select 'bb-256-100R1'
insert into @t select 'bb-256-100R9'
insert into @t select 'cc-678-123B'

select * from @t where 
patindex('%[^RB]',val)>0
and
patindex('%R[0-9]',val)=0

[-狙击手-]

declare @s table(col varchar(100))
insert @s 
select 
'qq-001-100' union select  
'qq-001-100R' union select  
'bb-123-234' union select  
'bb-256-100R0' union select  
'bb-256-100R1' union select  
'bb-256-100R9' union select  
'cc-678-123B'



select *
from @s
where not (PATINDEX( '%[RB]',col) > 0 or PATINDEX( '%[R][0-9]',col) > 0)