CREATE OR REPLACE FUNCTION FUN_CODE_SPLIT(STR1 VARCHAR2)
/*
--创建人:
--创建日期:2014-12-31
--创建内容:分割枚举字符串值
--使用对象: DIM_CODE维度表的使用
--分割符号:使用分号进行分割
*/
RETURN VARCHAR2 AS
RE_STR VARCHAR2(4000); --返回值
STR2 VARCHAR2(4000);
FIRST_STR0 VARCHAR2(500);
FIRST_STR1 VARCHAR2(500);
FIRST_STR2 VARCHAR2(500);
FIRST_STR3 VARCHAR2(500);
CUNT_I NUMBER;
I NUMBER;
BEGIN
STR2 := STR1;
STR2 := REPLACE(STR2, CHR(10), CHR(32)); --换行符转空格
STR2 := REPLACE(STR2, ';', CHR(32)); --剔除原来的分号
STR2 := REPLACE(STR2, '、', ','); --把顿号转逗号
STR2 := REGEXP_REPLACE(STR2, '[ ]+', CHR(32)); --压缩空格
CUNT_I := LENGTH(STR2);
FIRST_STR1 := '';
FIRST_STR2 := '';
FIRST_STR3 := '';
I := 1;
RE_STR := '';
-----------****************----------------
LOOP
IF I>=2 THEN
FIRST_STR0 := SUBSTR(RE_STR, -1);
END IF;
FIRST_STR1 := SUBSTR(STR2, I, 1);
FIRST_STR2 := SUBSTR(STR2, I + 1, 1);
FIRST_STR3 := SUBSTR(STR2, I + 2, 1);
CASE
WHEN FIRST_STR1 IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND ASCII(FIRST_STR2) = '32'
AND FIRST_STR3 IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
THEN RE_STR := RE_STR || FIRST_STR1 || ';'; --处理分割时枚举尾部有数字的
WHEN LENGTHB(FIRST_STR1) > '1'
AND FIRST_STR2 IN ('(', ')', ',', '+')
AND LENGTHB(FIRST_STR3) > '1'
THEN RE_STR := RE_STR || FIRST_STR1; --处理字符串中几个特殊字符号
WHEN LENGTHB(FIRST_STR1) > '1'
AND FIRST_STR2 NOT IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND LENGTHB(FIRST_STR2) = '1'
AND FIRST_STR3 = '(' --
THEN RE_STR := RE_STR || FIRST_STR1 || ';'; --++处理字符串例如--员,(
WHEN LENGTHB(FIRST_STR1) > '1'
AND ASCII(FIRST_STR2) = '32'
AND FIRST_STR3 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN RE_STR := RE_STR || FIRST_STR1 || ';'; --处理分割是使用空格分隔
WHEN LENGTHB(FIRST_STR1) > '1'
AND LENGTHB(FIRST_STR2) = '1'
AND FIRST_STR2 NOT IN ('(', ')','0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND (FIRST_STR3 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
OR ASCII(FIRST_STR3)='32')
THEN RE_STR := RE_STR || FIRST_STR1 || ';'; --处理有分隔符,空格,括号的
WHEN LENGTHB(FIRST_STR1) > '1'
AND FIRST_STR2 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND (FIRST_STR3 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
OR LENGTHB(FIRST_STR3)>1)
THEN RE_STR := RE_STR || FIRST_STR1 || ';'; --处理没有任何分割符的(一个编号或两个变好)
-------------------------------
WHEN LENGTHB(FIRST_STR1) = '1' AND FIRST_STR1 = ','
AND LENGTHB(FIRST_STR0)>1
AND LENGTHB(FIRST_STR2)>1
THEN RE_STR := RE_STR || FIRST_STR1; --处理字符串(例如,例如)字符串中带逗号的
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 NOT IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND LENGTHB(FIRST_STR0) = '1'
AND FIRST_STR0 = ';'
THEN NULL; --处理字符串分号有别的字符号
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 NOT IN ('(',')','+','0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND (LENGTHB(FIRST_STR2)>1 OR LENGTHB(FIRST_STR3)>1 )
THEN NULL; --处理字符串(50:已拒绝)这种里面的符号
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 NOT IN ('(',')','+','0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND ASCII(FIRST_STR1) <> '32'
AND FIRST_STR2 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN NULL; --处理原始分割
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 NOT IN ('(',')','+','0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND ASCII(FIRST_STR1) <> '32'
AND ASCII(FIRST_STR2) = '32'
AND FIRST_STR3 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN NULL; --处理原始分割后面跟空格
-------------------------------
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 = '('
AND LENGTHB(FIRST_STR2) > 1
THEN RE_STR := RE_STR || FIRST_STR1; --字符串中括号处理
WHEN LENGTHB(FIRST_STR1) = '1'
AND FIRST_STR1 = ')'
AND LENGTHB(FIRST_STR2) = '1'
AND FIRST_STR2 NOT IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
AND FIRST_STR3 IN ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G',
'H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN RE_STR := RE_STR || FIRST_STR1 || ';';
ELSE
RE_STR := RE_STR || FIRST_STR1;
END CASE;
EXIT WHEN I > CUNT_I;
I := I + 1;
FIRST_STR1 := '';
FIRST_STR2 := '';
FIRST_STR3 := '';
END LOOP;
-----------****************----------------
RE_STR := REPLACE(RE_STR, CHR(32));
--RE_STR := RE_STR || ';';
RETURN RE_STR;
EXCEPTION
WHEN OTHERS THEN
NULL;
END FUN_CODE_SPLIT;