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;