一个sql语句 字段nbr nbr以8831开头 ,后4位不包括4或者7,sql怎样写?求解......
解决方案 »
- 在线求sql语句
- system和sys用户有什么区别?还有system 作为sysdba登陆后此时和sys又有什么区别?
- 请教:按用户数购买Oracle在实际应用中如果超出该用户数是何结果
- Oracle表问题
- 请介绍一些介绍Oracle中job方面的书或例子.谢谢
- tomcat启动报错,ORA-22905: cannot access rows from a non-nested table item?是什么原因?
- 请教sql的写法
- Oracle 客户端的配置问题
- 我在win2000 server中,安装7张盘的oracle 9i,为什么点setup.exe,执行不起来,也不报错?(在线等待)
- oarcle里面获取某一张表的所有字段的名称以及字段类型的sql语句怎么写?
- 如果想执行alter system flush shared_pool 需拥有什么权限?
- 大数据量的插入(结贴率)
nbr like '8831%' and (instr(substr(nbr,-4),'4')<0 or instr(substr(nbr,-4),'7')<0)
with temp as (
select '8831432568987' id from dual
union all
select '88313256898' id from dual
)
select id from temp t
where length(replace(t.id,'4','')) = length(t.id)
and length(replace(t.id,'7','')) = length(t.id)
and substr(t.id,0,4) = '8831';
where length(REGEXP_REPLACE(t.id,'4|7','')) = length(t.id)
and substr(t.id,0,4) = '8831';
SQL> select * from t_like;
ID NAME
----------- --------------------------------------------------------------------------------
1 88311234
2 88311235
2 88211236
SQL> select * from t_like t where t.name like '8831%' and substr(name,-4) not like '%7%' and substr(name,-4) not like '%4%';
ID NAME
----------- --------------------------------------------------------------------------------
2 88311235
SQL>
select * from 表 where nbr like '8831%' and
substr(nbr,length(nbr)-4) not like '%4%' and
substr(nbr,len(nbr)-4) not like '%7%'
select * from 表 where nbr like '8831%' and
substr(nbr,length(nbr)-4) not like '%4%' and
substr(nbr,len(nbr)-4) not like '%7%'