分割枚举值函数，oracle 数据库

shijiazhenghu

已于 2024-04-21 08:45:16 修改

阅读量509

点赞数 24

文章标签： hadoop 大数据数据仓库 oracle

于 2024-04-21 08:43:34 首次发布

本文链接：https://blog.csdn.net/shijiazhenghu/article/details/138021864

版权

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;