Oracle创建创建清洗效验邮箱函数

--创建清洗效验邮箱函数
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;