oracle 正则表达式

1. 有哪些是目前质检体系发现不了的问题(发生过,只是 是没有事先察觉)
2. 哪些是管理层需要了解的但现有质检体系满足不了的
3. 现有的质检体系介绍,哪些规




select *        from (
select t.platform as platform,
       t.version_id as version_id,
       t.version as oversion,
       regexp_substr(t.version, '^[[:digit:]\.]') as bigver,
       decode(regexp_substr(version, '^[[:digit:]\.]'),4,regexp_substr(version, '^[[:digit:]](\.[[:digit:]]+)+00$'),version) as version
  from dim_version t
where regexp_substr(t.version, '^[[:digit:]](\.[[:digit:]]+)+$') is not null
   and t.version != 'all'
   )
    where version is not null



select platform,pn,regexp_substr(t.pn, '^[[:alnum:]](\_)*([[:alnum:]]+)+$') ,pn_id from dim_pn t




select platform,
       pn,
       pn_id
  from dim_pn t
where regexp_substr(t.pn, '^[[:alnum:]]*_*[[:alnum:]]*$') is not null and t.pn !='all'



select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='first'
    and product='online'
    and platform in('anphone','win','anpad','')
    and length(deviceid)=40
    and regexp_extract(version,'^[1-4](\.)',1) != '.'
    and version not rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}\.[1-9]{1,2}0?00$)';

select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='first'
    and product='online'
    and platform in('win','mac','iphone','ipad','anpad','anphone')
    and regexp_extract(version,'^[1-4](\.)',1) = '.'
    and version  rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00)?$)';
    and length(deviceid)=40;


select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='first'
    and product='online'
    and platform in('win','mac','iphone','ipad','anpad','anphone')
    and version  rlike '(^4\.[0-9]{1,2}\.[0-9]{1,2})|(^4\.[0-9]{1,2}\.[0-9]{1,2}\.[1-9]0?00$)'
    and length(deviceid)=40;
   

select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='first'
    and product='online'
    and platform in('win','mac','iphone','ipad','anpad','anphone')
    and version  REGEXP '^4(\.[0-9]{1,2}){1,2}(\.[1-9]{1,2}0?00$)?'
    and length(deviceid)=40;
   

(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00$)?)

select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='first'
    and product='online'
    and platform in('anphone')
    and length(deviceid)=40
    and version  rlike '^[1-3](\.[0-9]+)+$';




(?(exp)yes|no)





^[1-9a-z]*_*[1-9a-z]*$

select
distinct platform,(case when platform='anphone' and (substr(pn,1,1)='1' or substr(pn,1,1)='2') then 'user-defined' else pn end) as pn
  from
    rc_pt
  where
    1=1
    and accessDay >= '20130704'
    and accessDay <= '20130704'
    and datatype='oc'
    and product='online'
    and platform in('win','mac','iphone','ipad','anpad','anphone')
    and length(deviceid)=40
    and pn rlike '^[a-zA-Z0-9_]+$';