526互联

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

发布时间 2023-06-30 20:41:55作者: 每天进步多一点

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;