How to split comma-separated values (CSV) into multiple rows in Oracle

To split comma-separated values (CSV) into multiple rows in Oracle, you can use REGEXP_SUBSTR, CONNECT BY LEVEL, or XMLTABLE.

---

1️⃣ Using CONNECT BY + REGEXP_SUBSTR (Recommended for Oracle 11g+)

This method splits a CSV column into rows using REGEXP_SUBSTR and CONNECT BY LEVEL.

Example Table (CSV Data)

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    skills VARCHAR2(200)
);

INSERT INTO employees VALUES (1, 'Alice', 'Java,SQL,Python');
INSERT INTO employees VALUES (2, 'Bob', 'C++,JavaScript');
INSERT INTO employees VALUES (3, 'Charlie', 'Python,R,Scala');

COMMIT;

---

Query to Split CSV into Rows

SELECT emp_id, emp_name,
       TRIM(REGEXP_SUBSTR(skills, '[^,]+', 1, LEVEL)) AS skill
FROM employees
CONNECT BY LEVEL <= REGEXP_COUNT(skills, ',') + 1
AND PRIOR emp_id = emp_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

Output:

EMP_ID	EMP_NAME	SKILL
1	Alice	Java
1	Alice	SQL
1	Alice	Python
2	Bob	C++
2	Bob	JavaScript
3	Charlie	Python
3	Charlie	R
3	Charlie	Scala

---

How It Works?

• REGEXP_SUBSTR(skills, '[^,]+', 1, LEVEL): Extracts the Nth value from CSV.
• CONNECT BY LEVEL <= REGEXP_COUNT(skills, ',') + 1: Runs a loop for each comma-separated value.
• PRIOR emp_id = emp_id: Ensures recursion stays within the same emp_id.
• PRIOR DBMS_RANDOM.VALUE IS NOT NULL: Prevents infinite loops in hierarchical queries.

---

2️⃣ Using CROSS APPLY with DBMS_SQL (Oracle 12c+)

In Oracle 12c+, you can use CROSS APPLY for better performance.

SELECT emp_id, emp_name, skill
FROM employees
CROSS APPLY (
    SELECT TRIM(COLUMN_VALUE) AS skill
    FROM TABLE(STRING_SPLIT(skills, ','))
);

This is only available in Oracle 12c and later.

---

3️⃣ Using XMLTABLE (Another Alternative)

If you have older Oracle versions, you can use XMLTABLE:

SELECT emp_id, emp_name, skill
FROM employees,
     XMLTABLE(('"' || REPLACE(skills, ',', '","') || '"') 
               COLUMNS skill VARCHAR2(100) PATH '.');

---

Which Method to Use?

Oracle Version	Method
10g & 11g	CONNECT BY LEVEL (Best)
12c & Later	CROSS APPLY or CONNECT BY LEVEL
Any Version	XMLTABLE (Works but less efficient)

Some more examples

Example 1

with test as

      (select '421907802490;421907672085;421911460415;421905464170;421907802292' col from dual)
    select regexp_substr(col, '[^;]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^;]+')) + 1; 

    

Example 2

with test as
      (select 'AN,AS,BR,JH,KO,N1,N2,OR,WB'  col from dual)
    select regexp_substr(col, '[^,]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^,]+')) + 1;