--创建清洗效验邮箱函数
CREATE OR REPLACE FUNCTION XIAOYAN_Y(PHONENUMBER IN VARCHAR2) RETURN VARCHAR2 AS xi VARCHAR2(30); FAN NUMBER(10); BEGIN SELECT REPLACE(REPLACE(REPLACE(SUBSTR(PHONENUMBER,INSTR(PHONENUMBER,'<'),INSTR(PHONENUMBER,'>')-INSTR(PHONENUMBER,'<')),' ',''),'<',''),'>','') INTO xi FROM dual; SELECT COUNT(1) INTO FAN FROM DUAL WHERE REGEXP_LIKE(xi, '^\w+((-\w+)|(\.\w+))*\@[A-Za-z0-9]+((\.|-)[A-Za-z0-9]+)*\.[A-Za-z0-9]+(;{1,1}\w+((-\w+)|(\.\w+))*\@[A-Za-z0-9]+((\.|-)[A-Za-z0-9]+)*\.[A-Za-z0-9]+)*$'); IF FAN > 0 THEN RETURN xi; ELSE RETURN ''; END IF; --异常处理 EXCEPTION WHEN OTHERS THEN RETURN ''; END; -- 使用函数 SELECT XIAOYAN_Y(' 段强博< w1234 [email protected]>') FROM dual;
标签:xi,RETURN,创建,Za,REPLACE,PHONENUMBER,效验,Oracle,z0 From: https://www.cnblogs.com/erguai/p/16853295.html