表中有一列,形式如下qq-001-100
qq-001-100R
bb-123-234
bb-256-100R0
bb-256-100R1
...
bb-256-100R9cc-678-123B
....在查询时排除以R,B,R0~R9结尾的数据.我写了一个[^RB],可是不能排除以R0~R9这样结尾的.
qq-001-100R
bb-123-234
bb-256-100R0
bb-256-100R1
...
bb-256-100R9cc-678-123B
....在查询时排除以R,B,R0~R9结尾的数据.我写了一个[^RB],可是不能排除以R0~R9这样结尾的.
解决方案 »
- 求一SQL语句?
- sql server 2008 express 能不能远程连接?
- 一条简单的SQL语句..怎么设置从某一行开始查询
- 关于触发器的问题
- 在打开表---显示所有行 中,将A字段显示的结果是二位小数:2.00,而不是2
- sql2005 在备份数据库的时候,客户端连接不上,是怎么回事?
- 大大们进!继续刚才的问题!
- 如建立一个约束
- 一个关于bcp(MS SQL)的问题?
- 新人求SQL SEVER 2008 R2 英文企业版序列号
- 菜鸟提问:形如select * from a where not exists(select 1 from b where a.name = b.name)中1代表什么?
- 交叉点为日期型数据的交叉表怎么用SQL语句实现
patindex('%[^RB]',val)>0
and
patindex('%[^R0]',val)>0
and
patindex('%[^R9]',val)>0
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 個資料列受到影響)
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
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)
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]'接分
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]'
请解释一下.谢谢