How to split comma separated string into rows

In some cases, we may need to convert string data into rows. You can use the following methods to achieve this.

1. Regular Expressions

One of the first methods that comes to mind is to use regular expressions.

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; 

2. XMLTABLE

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

3. APEX_STRING API

Lat but not least, I would like to mention about APEX’s split function. I strongly recommend you take a look at the APEX_STRING api, it contains useful functions. APEX_STRING.SPLIT function is one of them. It is always beneficial to install APEX even if you don’t use it. Oracle APEX is a fully supported, no-cost feature of the Oracle database across all its versions.

SELECT * FROM APEX_STRING.SPLIT('YOUR,COMMA,SEPARATED,STRING', ',');

I hope you find this post helpful. 🙂