日期:2014-05-17  浏览次数:21149 次

查询带"6666"的手机号.排除带66666,666666,666,6666666等手机号的查询语句.
查询手机号

把所有手机号里带 6666 的手机号都查出来

还要包括 13706066669 类似这种连着6 还有个6的号

排除 66666 666666 666的 都得排除..




select mobile from table_user where .......................

------解决方案--------------------
探讨
'6{5}' 能讲解下么...谢谢

------解决方案--------------------
SQL code

create table phoneno
(
pno varchar2(20)
);
insert into phoneno
select '13766666021' from dual
union
select '13760666601' from dual
union 
select '13766606660' from dual
;
commit;
select pno,
       substr(pno,instr(pno,'6666',1)),
       substr(substr(pno,instr(pno,'6666',1)),1,5),
       case when substr(substr(substr(pno,instr(pno,'6666',1)),1,5),1,4) = '6666'
                 and substr(substr(substr(pno,instr(pno,'6666',1)),1,5),5,1) <> '6'
            then 'YES'
            else 'NO' end
  from phoneno;