oracle 电话号码

CREATE OR REPLACE FUNCTION FK_TH(VAR VARCHAR2)
RETURN VARCHAR2
AS
V_LENGTHB NUMBER;
V_LENGTH NUMBER;
V_VAR VARCHAR2(100);
BEGIN


--替换原则
--如果字段中只为汉字，那么删除
--除掉汉字如果字段长度小于7删除
--数据按照中文圆角书写替换为英文半角字符
--字段中如果出现“， 、 或”改为' ;'空格键
V_VAR :=VAR;
SELECT LENGTHB(V_VAR),LENGTH(V_VAR) INTO V_LENGTHB,V_LENGTH FROM DUAL;


IF V_LENGTHB=V_LENGTH THEN
  RETURN V_VAR;
ELSE
V_VAR :=REPLACE(V_VAR,'或',';');
V_VAR :=REPLACE(V_VAR,'—','');
V_VAR :=REPLACE(V_VAR,'－','');
V_VAR :=REPLACE(V_VAR,'、',';');
V_VAR :=REPLACE(V_VAR,'，',';');
/*V_VAR :=REPLACE(V_VAR,'；',';');
V_VAR :=REPLACE(V_VAR,'１','1');
V_VAR :=REPLACE(V_VAR,'２','2');
V_VAR :=REPLACE(V_VAR,'３','3');
V_VAR :=REPLACE(V_VAR,'４','4');
V_VAR :=REPLACE(V_VAR,'５','5');
V_VAR :=REPLACE(V_VAR,'６','6');
V_VAR :=REPLACE(V_VAR,'７','7');
V_VAR :=REPLACE(V_VAR,'８','8');
V_VAR :=REPLACE(V_VAR,'９','9');
V_VAR :=REPLACE(V_VAR,'０','0');
V_VAR :=REPLACE(V_VAR,'（','(');
V_VAR :=REPLACE(V_VAR,'）',')');*/
V_VAR :=TO_SINGLE_BYTE(V_VAR);
V_VAR :=REPLACE(V_VAR,'0000','');
V_VAR :=REGEXP_REPLACE(V_VAR,'[^0-9a-zA-Z;()[:space:]]') ;
V_VAR :=REPLACE(V_VAR,'()','');
IF V_VAR IS NULL OR LENGTHB(V_VAR)< 7 THEN
  V_VAR:='';
  RETURN V_VAR;
  ELSE
  RETURN TRIM(V_VAR);
END IF;
--'；'
END IF;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29162273/viewspace-1651091/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29162273/viewspace-1651091/