Oracle 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;

经典参考：
https://blog.csdn.net/Hehuyi_In/article/details/104590160/