SQL> select 1+1 from dual 2 where instr(substr('123456789',-4,4),'6',1) = 0 3 and instr(substr('123456789',-4,4),'8',1) = 0 4 and instr(substr('123456789',-4,4),'9',1) = 0;
1+1 ----------
SQL> select 1+1 from dual where regexp_like('123456789','[^[6|8|9]]{4}$');
1+1 ----------这个比较简单
如果是10G用正则表达式,如果是9i用instr函数
SQL> select 1+1 from dual where regexp_like('12345','[^[6|8|9]]{4}$');
1+1 ---------- 这个错了,不懂正则
[SYS@ora10gr1] SQL>select * from( 2 select '123456666' as a from dual 3 union 4 select '131123435' from dual 5 union 6 select '12345a611' from dual 7 ) 8 where regexp_like(a,'[^6|8|9]{4}$');A --------- 131123435
正则表达式表达,最后四位不是 689是:'[^689]{4}$' 或者 '[^6|8|9]{4}$'[^689] 或者 '[^6|8|9] 表示不是 689 {4}表示 4 位 $ 表示结束的标志 可以用上面的sql 也可直接写[SYS@ora10gr1] SQL>select * from( 2 select '123456666' as a from dual 3 union 4 select '131123435' from dual 5 union 6 select '12345a611' from dual 7 ) 8 where regexp_like(a,'[^689]{4}$');A --------- 131123435
2 where instr(substr('123456789',-4,4),'6',1) = 0
3 and instr(substr('123456789',-4,4),'8',1) = 0
4 and instr(substr('123456789',-4,4),'9',1) = 0;
1+1
----------
1+1
----------这个比较简单
1+1
----------
这个错了,不懂正则
[SYS@ora10gr1] SQL>select * from(
2 select '123456666' as a from dual
3 union
4 select '131123435' from dual
5 union
6 select '12345a611' from dual
7 )
8 where regexp_like(a,'[^6|8|9]{4}$');A
---------
131123435
{4}表示 4 位
$ 表示结束的标志
可以用上面的sql
也可直接写[SYS@ora10gr1] SQL>select * from(
2 select '123456666' as a from dual
3 union
4 select '131123435' from dual
5 union
6 select '12345a611' from dual
7 )
8 where regexp_like(a,'[^689]{4}$');A
---------
131123435