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. In the example below, I used the semicolon.
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. Moreover, if you want to convert more data set into multiple rows. You can also 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, ',', '","') || '"'));