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)