分割字符串

SQL> WITH SPLIT_TEST AS
2 (SELECT 'ORACLE,MYSQL,HADOOP,PLANNING,DATASTAGE,10,11' AS SPLIT_STR FROM DUAL
3 UNION
4 SELECT 'SCOTT,HR,OE,2014,2015' AS SPLIT_STR FROM DUAL
5 )
6 SELECT REPLACE(SPLIT_STR,',',CHR(10)) S_WORD FROM SPLIT_TEST
7 ;
S_WORD
--------------------------------------------
ORACLE
MYSQL
HADOOP
PLANNING
DATASTAGE
10
11
SCOTT
HR
OE
2014
2015

 

 

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

 

WITH T_STR_ROW AS
(SELECT 1 AS ID, 'v11|$|v12|$|v13|$|' AS STR FROM DUAL)
SELECT C.LV,
RTRIM(REGEXP_SUBSTR(T.STR, '.*?\|\$\|', 1, C.LV), '\|\$\|') AS CV
FROM (SELECT STR,
LENGTH(REGEXP_REPLACE(STR,
'[^' || CHR(124) || CHR(36) || CHR(124) || ']',
NULL)) / 3 AS CNT
FROM T_STR_ROW
WHERE ID = '1') T
INNER JOIN (SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <=
(SELECT LENGTH(REGEXP_REPLACE(STR,
'[^' || CHR(124) ||
CHR(36) || CHR(124) || ']',
NULL)) / 3 AS CNT
FROM T_STR_ROW
WHERE ID = '1')) C
ON C.LV <= T.CNT;