神奇,吉祥号码查找

最近项目需要从号码里头找出一些特定格式的吉祥号码.
笔记一下. 本来函数是返回Boolean的,发现不能用在where 的条件,所以按照鬼佬的提示,改成返回 'Y','N'. where 条件是 where fliter_gsm_number('130000000','aaaa') ='Y'

在Oracle10g on AIX 测试通过

入参:
号码,吉祥号码格式,如 'AAA', '8AAA' ,'ABAB','AABB' ,'ABCD'
目前支持
[list]
[*]连号 如 00000,11111.
[*]A数字A数字
[*]A数字..A
[*]步步高升系列 ABCDE,EDCBA
[/list]
欢迎补充 >.<

CREATE OR REPLACE FUNCTION fliter_gsm_number(gsm_number in varchar2,exp in varchar2) return varchar2 is
regexp varchar2(200);
i        number;
icount    number;
slen      number;
elen      number;
alpha     varchar2(100);
pgsm_number  varchar2(100);
begin
   --号码格式长度不能超过号码长度
   if length(exp) > length(gsm_number) then
   return 'N';
   end if;
   pgsm_number := gsm_number;
  --判断号码格式
  --连号 至少2位匹配 AA 或 BB 
  if regexp_like(exp,'^([[:digit:]]*)([[:alpha:]])\2+([[:digit:]]*)') then
     alpha := regexp_substr(exp,'([[:alpha:]])\1+');
     slen := instr(exp,alpha);
     elen := slen + length(alpha)-1 ;
     if slen> 1 then
      regexp := substr(exp,1,slen-1);
     end if;
     regexp := regexp||'([[:digit:]])\1{'|| to_char(elen-slen) ||'}'; --'([[:digit:]])(\1){1}'
     if elen<length(exp) then
      regexp := regexp||substr(exp,elen+1);
     end if;
  --ABBA,ABBBBBA
  elsif regexp_like(exp,'^([[:alpha:]])([[:alpha:]])\2+\1?') then
        alpha := regexp_substr(exp,'([[:alpha:]])\1+');
        regexp := '([[:digit:]])([[:digit:]])\2{'||to_char(length(alpha)-1)||'}';
        if substr(exp,-1,1) = substr(exp,1,1) then
        regexp := regexp||'(\1)';
        end if;  
  --ABAB  7777 也会匹配 有必要可以在输出时再判断一次
  elsif regexp_like(exp,'^([[:alpha:]][[:alpha:]])\1+') and substr(exp,1,1)<>substr(exp,2,1) then
     regexp := '([[:digit:]])([[:digit:]])(\1\2)' ;
     if length(exp) > 4 then
        regexp := regexp||'{'||(length(exp)/2-1)||'}';
      end if;   
  --A数字A数字...
  elsif regexp_like(exp,'^([[:alpha:]][[:digit:]])\1+') then
     regexp := '([[:digit:]])('||substr(exp,2,1)||')';
     if length(exp) > 2 then
        regexp := regexp||'(\1\2){'||(length(exp)/2-1)||'}';
     end if;
  --AABB 
  elsif regexp_like(exp,'^([[:alpha:]]{2})([[:alpha:]]{2})(\1\2)+') then
     regexp := '(([[:digit:]])\1([:digit:])\2)' ; 
     if length(exp) > 4 then
        regexp := regexp||'(\1\2){'||(length(exp)/2-1)||'}';
     end if;

  --A数字..A
  elsif regexp_like(exp,'^([[:alpha:]])([[:digit:]])+\1') then
     regexp := '([[:digit:]])'||substr(exp,2,length(exp)-2)||'\1';

  --步步高升系列 t
  -- ABC(DEF..) 至少以ABC开头
  elsif upper(substr(exp,1,3)) = 'ABC' then
      alpha := regexp_substr(exp,'[[:alpha:]]+');
      if length(gsm_number) <> length(alpha) and instr(gsm_number,regexp_substr(exp,'[[:digit:]]+$'),-1) <= 0 then
         return 'N';
      else 
         pgsm_number := regexp_substr(regexp_substr(exp,'^[[:digit:]]+') || gsm_number,'[[:digit:]]+');
      end if;
      icount := 1;
      --遍历号码
      for i in 1..length(pgsm_number) loop
       if icount = length(alpha)  then
           return 'Y';
       end if;
       if  to_number(substr(pgsm_number,i,1)) +1 =to_number(substr(pgsm_number,i+1,1)) then
       icount := icount +1;
       -- 如果不能连续符合条件即返回N
       else
          if icount >1 then
            return 'N';
          end if; 
       end if;
      end loop;
      return 'N';
  --(..ED)CBA 至少以CBA结尾
  elsif upper(substr(exp,-3)) = 'CBA' then
      alpha := regexp_substr(exp,'[[:alpha:]]+');
      if length(gsm_number) <> length(alpha) and instr(gsm_number,regexp_substr(exp,'^[[:digit:]]+')) <= 0 then
         return 'N';
      else 
         pgsm_number := regexp_substr(gsm_number,'[[:digit:]]+'||regexp_substr(exp,'^[[:digit:]]+'));  
      end if;
      icount := 1;
      --遍历号码
      for i in 1..length(pgsm_number) loop
       if icount = length(alpha) then
       return 'Y';
       end if;
       if  to_number(substr(pgsm_number,i,1)) -1 =to_number(substr(pgsm_number,i+1,1)) then
       icount := icount +1;
       -- 如果不能连续符合条件即返回N
        else
          if icount >1 then
            return 'N';
          end if; 
       end if;
      end loop;
      return 'N';

  -----------其他----------------
  else
      return 'N';
  end if;
--  dbms_output.put_line('reg:'||regexp);
  -- 是否匹配正则
  if regexp_like(gsm_number,regexp)
  then
     return 'Y';
  else
     return 'N';   
  end if;

end fliter_gsm_number;

测试

select fliter_gsm_number('13077127788','AABB') from dual; --Y
select fliter_gsm_number('13077127788','AA1') from dual; --Y

select * from table_numbers where fliter_gsm_number(gsm_number,'AABB') = 'Y';