WITH SPLIT_TEST AS
(SELECT ‘ORACLE,MYSQL,HADOOP,PLANNING,DATASTAGE,10,11‘ AS SPLIT_STR FROM DUAL)
SELECT REGEXP_SUBSTR(SPLIT_STR, ‘\w+‘, 1, LEVEL) AS BI1,
REGEXP_SUBSTR(SPLIT_STR, ‘[^,]+‘, 1, LEVEL) AS BI2,
LEVEL
FROM (SELECT SPLIT_STR,
LENGTH(SPLIT_STR) L1,
LENGTH(REPLACE(SPLIT_STR, ‘,‘, ‘‘)) L2
FROM SPLIT_TEST) A
CONNECT BY LEVEL <= L1 - L2 + 1

BI1                BI2                 LEVEL
----------      --------------- -------
ORACLE         ORACLE          1
MYSQL           MYSQL            2
HADOOP        HADOOP          3
PLANNING      PLANNING        4
DATASTAGE  DATASTAGE     5
10                10                   6
11                11                   7

分割字符串