如果一条语句完成的话,只想到一种蠢办法,穷举SQL> select * from t; NUM --------------------------------------- 6325566 6329696 6324457 6329901 6326699 63255556 rows selectedSQL> SQL> select * from t where 2 instr(num,'00')>0 or instr(num,'11')>0 or 3 instr(num,'22')>0 or instr(num,'33')>0 or 4 instr(num,'44')>0 or instr(num,'55')>0 or 5 instr(num,'66')>0 or instr(num,'77')>0 or 6 instr(num,'88')>0 or instr(num,'99')>0; NUM --------------------------------------- 6325566 6324457 6329901 6326699 6325555SQL>
结合循环来写WHEN I=I+1 写存储过程或者 用REPLACE函数取出比较 反正你是定长吧
select * from test where regexp_like(NUM,'([[:alnum:]])\1');
drop table tx2; create table tx2(a int);insert into tx2 values(6325566); insert into tx2 values(6329696 ); insert into tx2 values(6324457 ); insert into tx2 values(6329901 ); insert into tx2 values(6326699 ); insert into tx2 values(6325555 );
SELECT A FROM (SELECT INSTR(A, CC) P, A FROM TX2, (SELECT CHR(47 + ROWNUM) || CHR(47 + ROWNUM) CC FROM DUAL CONNECT BY ROWNUM <= 10) X) WHERE P > 0 GROUP BY A; 输出: 6326699 6324457 6325566 6329901 6325555
Select * From Tx2 t Where Substr(To_Char(t.a), 4, 1) = Substr(To_Char(t.a), 5, 1) Or Substr(To_Char(t.a), 5, 2) = Substr(To_Char(t.a), 6, 3) Or Substr(To_Char(t.a), 6, 3) = Substr(To_Char(t.a), 7, 2)
---------------------------------------
6325566
6329696
6324457
6329901
6326699
63255556 rows selectedSQL>
SQL> select * from t where
2 instr(num,'00')>0 or instr(num,'11')>0 or
3 instr(num,'22')>0 or instr(num,'33')>0 or
4 instr(num,'44')>0 or instr(num,'55')>0 or
5 instr(num,'66')>0 or instr(num,'77')>0 or
6 instr(num,'88')>0 or instr(num,'99')>0; NUM
---------------------------------------
6325566
6324457
6329901
6326699
6325555SQL>
写存储过程或者
用REPLACE函数取出比较
反正你是定长吧
drop table tx2;
create table tx2(a int);insert into tx2 values(6325566);
insert into tx2 values(6329696 );
insert into tx2 values(6324457 );
insert into tx2 values(6329901 );
insert into tx2 values(6326699 );
insert into tx2 values(6325555 );
SELECT A
FROM (SELECT INSTR(A, CC) P, A
FROM TX2,
(SELECT CHR(47 + ROWNUM) || CHR(47 + ROWNUM) CC
FROM DUAL
CONNECT BY ROWNUM <= 10) X)
WHERE P > 0
GROUP BY A;
输出:
6326699
6324457
6325566
6329901
6325555
From Tx2 t
Where Substr(To_Char(t.a), 4, 1) = Substr(To_Char(t.a), 5, 1)
Or Substr(To_Char(t.a), 5, 2) = Substr(To_Char(t.a), 6, 3)
Or Substr(To_Char(t.a), 6, 3) = Substr(To_Char(t.a), 7, 2)