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) V_VAR:='';
RETURN V_VAR;
ELSE
RETURN TRIM(V_VAR);
END IF;
--'；'
END IF;
END;