Oracle+MySQL || 判断字段是否为数字

1、判断字段是否为数字

Oracle参考:

  -- modify20201111 中国护照 D S P E G H K M 开头 3-9位为数字
   case when ppie.id_type='2' and (substr(ppie.id_no ,1,1) not in ('D','S','P', 'E', 'G', 'H' ,'K','M') or not regexp_like(substr(ppie.id_no,3,9),'^[0-9]+[0-9]$')) then '51' else null end IDENTIFYTYPE, --证件类型

MySQL参考：

  select l.id , l.policyNo , l.applyNo , l.insuredName ,  l.identifynumber , l.identifyTYpe , l.sex 
  from prppinsuredidvlist l 
  where l.identifyTYpe ='07' -- 中国护照
  and (substr(l.identifynumber,1,1) not in ('D','S','P', 'E', 'G', 'H' ,'K','M')or substr(l.identifynumber,3,9)  not REGEXP '(^[0-9]+.[0-9]+$)|(^[0-9]$)' )
  and EXISTS (select 1 from prpcopymain m where m.policyno=l.policyno and m.historyflag = '0' )

=======================================
Oracle参考博客：https://www.cnblogs.com/forzheng/p/8398999.html
MySQL参考博客：https://blog.csdn.net/mynamepg/article/details/83060289