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, ',', '","') || '"'));
