数据库查询使用正则表达式

REGEXP_REPLACE正则表达式替换

 

应用举例1

应用思路：将字符串中的全部数字替换成%，抽取特征码 以形成问题模式

应用实现：REGEXP_REPLACE(WO.REMARKS,'[0-9]+','%')

 

具体如下：

--新增问题模式
    SELECT 7000,REGEXP_REPLACE(WO.REMARKS,'[0-9]+','%') 问题模式, COUNT(1) cnt
      --into vCnt
      FROM WO, SO
     WHERE 1 = 1
       AND NOT EXISTS (SELECT 1 FROM ISSUE_PATTERN IP WHERE IP.ITEM_ID =7 AND STS ='A' AND WO.REMARKS LIKE '%'||IP.PATTERN_STR||'%')
       AND WO.SO_NBR =SO.SO_NBR
       AND (WO.RUN_STS ='D' AND WO.BUSI_STS ='I')
       AND SO.SO_STS NOT IN('C','R')
       AND SO.STS ='A'
       GROUP BY REGEXP_REPLACE(WO.REMARKS,'[0-9]+','%')
       
ORDER BY cnt DESC

 

应用举例2 

--将所有客户服务编码为581 但业务名称定义成[***解锁]的改成[***加锁]
UPDATE BUSINESS B
 SET NAME = REGEXP_REPLACE(B.NAME,'解锁','加锁')
 WHERE 1 = 1
   AND B.CHG_SERV_SPEC_ID IN (581)