1. SUBSTR, INSTR 이용
SELECT T.PJTCD
FROM (SELECT TRIM(DECODE(NO , 1, PJTCD01
, 2, PJTCD02
, 3, PJTCD03
, 4, PJTCD04
, 5, PJTCD05
, 6, PJTCD06
, 7, PJTCD07
, 8, PJTCD08
, 9, PJTCD09
, 10, PJTCD10
, 11, PJTCD11
, 12, PJTCD12
, 13, PJTCD13
, 14, PJTCD14
, 15, PJTCD15
, 16, PJTCD16
, 17, PJTCD17
, 18, PJTCD18
, 19, PJTCD19
, 20, PJTCD20)) AS PJTCD
FROM (SELECT NO
, SUBSTR(PROJECT_LIST, 1 , DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 1) - 1), 1, INSTR(PROJECT_LIST, ',', 1, 1) - 1, LENGTH(PROJECT_LIST))) AS PJTCD01
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 1), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 1)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 2) - INSTR(PROJECT_LIST, ',', 1, 1) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 2) - INSTR(PROJECT_LIST, ',', 1, 1) - 1)) AS PJTCD02
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 2), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 2)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 3) - INSTR(PROJECT_LIST, ',', 1, 2) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 3) - INSTR(PROJECT_LIST, ',', 1, 2) - 1)) AS PJTCD03
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 3), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 3)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 4) - INSTR(PROJECT_LIST, ',', 1, 3) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 4) - INSTR(PROJECT_LIST, ',', 1, 3) - 1)) AS PJTCD04
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 4), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 4)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 5) - INSTR(PROJECT_LIST, ',', 1, 4) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 5) - INSTR(PROJECT_LIST, ',', 1, 4) - 1)) AS PJTCD05
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 5), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 5)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 6) - INSTR(PROJECT_LIST, ',', 1, 5) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 6) - INSTR(PROJECT_LIST, ',', 1, 5) - 1)) AS PJTCD06
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 6), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 6)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 7) - INSTR(PROJECT_LIST, ',', 1, 6) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 7) - INSTR(PROJECT_LIST, ',', 1, 6) - 1)) AS PJTCD07
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 7), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 7)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 8) - INSTR(PROJECT_LIST, ',', 1, 7) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 8) - INSTR(PROJECT_LIST, ',', 1, 7) - 1)) AS PJTCD08
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 8), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 8)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 9) - INSTR(PROJECT_LIST, ',', 1, 8) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 9) - INSTR(PROJECT_LIST, ',', 1, 8) - 1)) AS PJTCD09
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 9), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 9)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 10) - INSTR(PROJECT_LIST, ',', 1, 9) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 10) - INSTR(PROJECT_LIST, ',', 1, 9) - 1)) AS PJTCD10
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 10), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 10)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 11) - INSTR(PROJECT_LIST, ',', 1, 10) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 11) - INSTR(PROJECT_LIST, ',', 1, 10) - 1)) AS PJTCD11
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 11), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 11)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 12) - INSTR(PROJECT_LIST, ',', 1, 11) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 12) - INSTR(PROJECT_LIST, ',', 1, 11) - 1)) AS PJTCD12
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 12), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 12)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 13) - INSTR(PROJECT_LIST, ',', 1, 12) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 13) - INSTR(PROJECT_LIST, ',', 1, 12) - 1)) AS PJTCD13
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 13), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 13)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 14) - INSTR(PROJECT_LIST, ',', 1, 13) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 14) - INSTR(PROJECT_LIST, ',', 1, 13) - 1)) AS PJTCD14
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 14), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 14)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 15) - INSTR(PROJECT_LIST, ',', 1, 14) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 15) - INSTR(PROJECT_LIST, ',', 1, 14) - 1)) AS PJTCD15
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 15), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 15)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 16) - INSTR(PROJECT_LIST, ',', 1, 15) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 16) - INSTR(PROJECT_LIST, ',', 1, 15) - 1)) AS PJTCD16
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 16), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 16)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 17) - INSTR(PROJECT_LIST, ',', 1, 16) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 17) - INSTR(PROJECT_LIST, ',', 1, 16) - 1)) AS PJTCD17
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 17), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 17)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 18) - INSTR(PROJECT_LIST, ',', 1, 17) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 18) - INSTR(PROJECT_LIST, ',', 1, 17) - 1)) AS PJTCD18
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 18), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 18)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 19) - INSTR(PROJECT_LIST, ',', 1, 18) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 19) - INSTR(PROJECT_LIST, ',', 1, 18) - 1)) AS PJTCD19
, SUBSTR(PROJECT_LIST, DECODE(INSTR(PROJECT_LIST, ',', 1, 19), 0, NULL, INSTR(PROJECT_LIST, ',', 1, 19)) + 1, DECODE(SIGN(INSTR(PROJECT_LIST, ',', 1, 20) - INSTR(PROJECT_LIST, ',', 1, 19) - 1), -1, LENGTH(PROJECT_LIST), INSTR(PROJECT_LIST, ',', 1, 20) - INSTR(PROJECT_LIST, ',', 1, 19) - 1)) AS PJTCD20
FROM (SELECT 'S100,C100,Q100,D100,Y250,IS14,Y290,Q150,C150' AS PROJECT_LIST FROM DUAL
UNION ALL
SELECT 'Z003,, X998' AS PROJECT_LIST FROM DUAL)
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 20) ON 1 = 1)) T
WHERE T.PJTCD IS NOT NULL;
2. REGEXP_SUBSTR 이용
SELECT T.PJTCD
FROM (SELECT TRIM(DECODE(NO , 1, PJTCD01
, 2, PJTCD02
, 3, PJTCD03
, 4, PJTCD04
, 5, PJTCD05
, 6, PJTCD06
, 7, PJTCD07
, 8, PJTCD08
, 9, PJTCD09
, 10, PJTCD10
, 11, PJTCD11
, 12, PJTCD12
, 13, PJTCD13
, 14, PJTCD14
, 15, PJTCD15
, 16, PJTCD16
, 17, PJTCD17
, 18, PJTCD18
, 19, PJTCD19
, 20, PJTCD20)) AS PJTCD
FROM (SELECT NO
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 1) AS PJTCD01
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 2) AS PJTCD02
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 3) AS PJTCD03
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 4) AS PJTCD04
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 5) AS PJTCD05
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 6) AS PJTCD06
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 7) AS PJTCD07
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 8) AS PJTCD08
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 9) AS PJTCD09
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 10) AS PJTCD10
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 11) AS PJTCD11
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 12) AS PJTCD12
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 13) AS PJTCD13
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 14) AS PJTCD14
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 15) AS PJTCD15
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 16) AS PJTCD16
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 17) AS PJTCD17
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 18) AS PJTCD18
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 19) AS PJTCD19
, REGEXP_SUBSTR(PROJECT_LIST, '[^,]*[^,]', 1, 20) AS PJTCD20
FROM (SELECT 'S100,C100,Q100,D100,Y250,IS14,Y290,Q150,C150' AS PROJECT_LIST FROM DUAL
UNION ALL
SELECT 'Z003,, X998' AS PROJECT_LIST FROM DUAL)
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 20) ON 1 = 1)) T
WHERE T.PJTCD IS NOT NULL;
PJTCD
--------------------------------------------
S100
Z003
C100
Q100
X998
D100
Y250
IS14
Y290
Q150
C150