--正则不会,用substr给你写个: WHERE InStr(SubStr(num,4,4),'4') >0 AND SubStr(num,11,-4) IN('9999','0000','6666','8888')
SQL> with tb as 2 (select '13914329999' id from dual union all 3 select '13514329999' id from dual union all 4 select '13914320000' id from dual union all 5 select '13914320232' id from dual union all 6 select '13912329999' id from dual) 7 select * from tb where regexp_like(id,'[0|6|8|9]{4}') 8 and regexp_instr(substr(id,4,4),'4+')>0 9 /ID ----------- 13914329999 13514329999 13914320000
感谢两位的回复。再请问下wkc168,如果只用regexp_like()可以实现吗?
1 with tb as( 2 select '13914329999' n from dual union all 3 select '13513246666' from dual union all 4 select '13913420000' from dual union all 5 select '13923329999' from dual union all -- filter 6 select '13912340689' from dual union all -- filter 7 select '13988881234' from dual union all -- filter 8 select '13941238888' from dual 9 ) 10 select * from tb 11 where regexp_like(n,'([0|6|8|9]{1})\1\1\1$') 12* and instr(substr(n,4,4),'4')>0 SQL> /N ----------- 13914329999 13513246666 13913420000 13941238888
在2楼的基础上改了一下 SQL> with tb as 2 (select '13914329999' id from dual union all 3 select '13514329999' id from dual union all 4 select '13914320000' id from dual union all 5 select '13914320232' id from dual union all 6 select '13666647777' id from dual union all 7 select '13912329999' id from dual) 8 select * from tb where regexp_like(id,'[0689]{4}$') 9 and regexp_instr(id,'4+',4)>0 10 /
ID ----------- 13914329999 13514329999 13914320000
SQL>
select * from tb where regexp_like(id,'([0689])\1\1\1$') and regexp_instr(id,'4+',4)>0
--正则不会,用substr给你写个:
WHERE InStr(SubStr(num,4,4),'4') >0 AND SubStr(num,11,-4) IN('9999','0000','6666','8888')
SQL> with tb as
2 (select '13914329999' id from dual union all
3 select '13514329999' id from dual union all
4 select '13914320000' id from dual union all
5 select '13914320232' id from dual union all
6 select '13912329999' id from dual)
7 select * from tb where regexp_like(id,'[0|6|8|9]{4}')
8 and regexp_instr(substr(id,4,4),'4+')>0
9 /ID
-----------
13914329999
13514329999
13914320000
1 with tb as(
2 select '13914329999' n from dual union all
3 select '13513246666' from dual union all
4 select '13913420000' from dual union all
5 select '13923329999' from dual union all -- filter
6 select '13912340689' from dual union all -- filter
7 select '13988881234' from dual union all -- filter
8 select '13941238888' from dual
9 )
10 select * from tb
11 where regexp_like(n,'([0|6|8|9]{1})\1\1\1$')
12* and instr(substr(n,4,4),'4')>0
SQL> /N
-----------
13914329999
13513246666
13913420000
13941238888
SQL> with tb as
2 (select '13914329999' id from dual union all
3 select '13514329999' id from dual union all
4 select '13914320000' id from dual union all
5 select '13914320232' id from dual union all
6 select '13666647777' id from dual union all
7 select '13912329999' id from dual)
8 select * from tb where regexp_like(id,'[0689]{4}$')
9 and regexp_instr(id,'4+',4)>0
10 /
ID
-----------
13914329999
13514329999
13914320000
SQL>
and regexp_instr(id,'4+',4)>0