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/