Oracle REGEXP_SUBSTR、CONNECT BY根据特定字符拆分字符串

Oracle CONNECT BY根据特定字符拆分字符串

1、一行

SELECT T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL
  FROM (SELECT '101' ID, 'A,B' VALS FROM DUAL) T
CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+');

2、多行
2-1、如果ID唯一不重复：

SELECT T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL
  FROM (
          SELECT '101' ID, 'A,B' VALS FROM DUAL UNION ALL
          SELECT '102' ID, 'X,X' VALS FROM DUAL
        ) T
CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+')
          AND PRIOR T.ID = T.ID
          AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;

2-2、如果ID有重复:

SELECT T.RM, T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL
  FROM (SELECT ROWNUM RM, A.* FROM 
            (
              SELECT '101' ID, 'A,B' VALS FROM DUAL UNION ALL
              SELECT '101' ID, 'C,D' VALS FROM DUAL UNION ALL
              SELECT '102' ID, 'X,X' VALS FROM DUAL
            ) A
        ) T
CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+')
          AND PRIOR T.RM = T.RM 
          AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;

3、Kingbase数据库 DBMS_RANDOM.VALUE() -> random()

SELECT T.RM, T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL
  FROM (SELECT ROWNUM RM, A.* FROM 
            (
              SELECT '101' ID, 'A,B' VALS FROM DUAL UNION ALL
              SELECT '101' ID, 'C,D' VALS FROM DUAL UNION ALL
              SELECT '102' ID, 'X,X' VALS FROM DUAL
            ) A
        ) T
CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+')
          AND PRIOR T.RM = T.RM 
          AND PRIOR random() IS NOT NULL;

4、REGEXP_SUBSTR拆分字符串综合示例

--数据准备
WITH LOT_RUN_CARD_STEP AS
 (SELECT 1111 AS RUN_CARD_RRN, 1 AS STEP_SERIAL_NO, '1002,1003,1004' AS UNIT_RRNS, '1002,1003,1004' AS MEASUREMENT_UNIT_RRNS FROM DUAL
  UNION ALL SELECT 1111, 2, '1002,1003,1004', NULL FROM DUAL),
UNIT AS
 (SELECT '1002' AS UNIT_RRN, 'A001#02' AS UNIT_ID FROM DUAL
  UNION ALL SELECT '1003', 'A001#03' FROM DUAL
  UNION ALL SELECT '1004', 'A001#04' FROM DUAL
 )

--假定RUN_CARD_RRN与STEP_SERIAL_NO是联合主键
 SELECT 
       T.RUN_CARD_RRN,
       T.STEP_SERIAL_NO,
       T.UNIT_RRNS,
       REGEXP_SUBSTR(T.UNIT_RRNS, '[^,]+', 1, LEVEL) AS UNIT_RRN
   FROM 
       LOT_RUN_CARD_STEP T
   CONNECT BY 
       REGEXP_SUBSTR(T.UNIT_RRNS, '[^,]+', 1, LEVEL) IS NOT NULL
       AND PRIOR T.RUN_CARD_RRN = T.RUN_CARD_RRN
       AND PRIOR T.STEP_SERIAL_NO = T.STEP_SERIAL_NO
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

SELECT 
    RUN_CARD_RRN,
    STEP_SERIAL_NO,
    UNIT_RRNS,
    (SELECT LISTAGG(U.UNIT_ID, ',') WITHIN GROUP (ORDER BY INSTR(',' || L.UNIT_RRNS || ',', ',' || U.UNIT_RRN || ','))
     FROM UNIT U
     WHERE ',' || L.UNIT_RRNS || ',' LIKE '%,' || U.UNIT_RRN || ',%') AS UNIT_IDS,
    MEASUREMENT_UNIT_RRNS,
    (SELECT LISTAGG(U.UNIT_ID, ',') WITHIN GROUP (ORDER BY INSTR(',' || L.MEASUREMENT_UNIT_RRNS || ',', ',' || U.UNIT_RRN || ','))
     FROM UNIT U
     WHERE ',' || L.MEASUREMENT_UNIT_RRNS || ',' LIKE '%,' || U.UNIT_RRN || ',%') AS MEASUREMENT_UNIT_IDS
FROM 
    LOT_RUN_CARD_STEP L WHERE L.RUN_CARD_RRN = 1111;

SELECT 
    L.RUN_CARD_RRN,
    L.STEP_SERIAL_NO,
    L.UNIT_RRNS,
    L.MEASUREMENT_UNIT_RRNS,
    ( SELECT LISTAGG(U.UNIT_ID, ',') WITHIN GROUP (ORDER BY R.RN)
        FROM ( SELECT REGEXP_SUBSTR(L.UNIT_RRNS, '[^,]+', 1, LEVEL) AS UNIT_RRN,LEVEL AS RN FROM DUAL 
               CONNECT BY REGEXP_SUBSTR(L.UNIT_RRNS, '[^,]+', 1, LEVEL) IS NOT NULL
        ) R JOIN UNIT U ON U.UNIT_RRN = R.UNIT_RRN
    ) AS UNIT_IDS,
    ( SELECT LISTAGG(U.UNIT_ID, ',') WITHIN GROUP (ORDER BY R.RN)
        FROM ( SELECT REGEXP_SUBSTR(L.MEASUREMENT_UNIT_RRNS, '[^,]+', 1, LEVEL) AS UNIT_RRN,LEVEL AS RN FROM DUAL 
               CONNECT BY REGEXP_SUBSTR(L.MEASUREMENT_UNIT_RRNS, '[^,]+', 1, LEVEL) IS NOT NULL
        ) R JOIN UNIT U ON U.UNIT_RRN = R.UNIT_RRN
    ) AS MEASUREMENT_UNIT_IDS
FROM 
    LOT_RUN_CARD_STEP L
WHERE 
    L.RUN_CARD_RRN = 1111;

6、字符串基础拆分对比

SELECT REGEXP_SUBSTR(',1,,,2,', '[^,]+', 1, LEVEL) AS UNIT_RRN, LEVEL AS RN FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(',1,,,2,', ',') +1;

--安全的正则表达式：如果无法控制输入格式，可以使用更健壮的拆分逻辑
SELECT REGEXP_SUBSTR(',1,,,2,', '[^,]+', 1, LEVEL) AS UNIT_RRN, LEVEL AS RN FROM DUAL
CONNECT BY REGEXP_SUBSTR(',1,,,2,', '[^,]+', 1, LEVEL) IS NOT NULL;

SELECT REGEXP_SUBSTR(',1,,,2,', '[^,]+', 1, LEVEL) AS UNIT_RRN, LEVEL AS RN FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(',1,,,2,', '[^,]+');

经典参考：
https://blog.youkuaiyun.com/Hehuyi_In/article/details/104590160/