How to split comma separated string into rows

In rare cases we might need to convert string data into rows. So, you can simply do this using the code below.

SELECT 
    REGEXP_SUBSTR('A,B,C,D,E','[^,]+', 1, LEVEL) COL1 
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('A,B,C,D,E', ',') + 1;

If your string is not comma separated, you can change the “,” sign into the character that you use in the string.

WITH 
    Q_STR AS (SELECT 'YOUR,COMMA,SEPARATED,STRING' STR FROM DUAL)
SELECT 
    REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) COL1 
FROM Q_STR
CONNECT BY LEVEL <= REGEXP_COUNT(STR, ',') + 1; 

Regular expressions are not the only way to do this. As it is known, regular expressions are cpu intensive. Therefore, if you want to convert more data set into multiple rows. You can use XMLTABLE operator as follows.

WITH 
    Q_STR AS (SELECT 'YOUR,COMMA,SEPARATED,STRING' STR FROM DUAL)
SELECT 
    TRIM(COLUMN_VALUE) COL1
FROM Q_STR, 
XMLTABLE(('"' || REPLACE(STR, ',', '","') || '"'));