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;