How to Efficiently Split Comma-Separated Values into Multiple Rows in Oracle Without Duplicates?

Oracle: Efficiently Split Comma-Separated Values into Multiple Rows

In Oracle queries, when splitting comma-separated data into multiple rows using CONNECT BY and regular expressions, duplicate rows can arise. To overcome this issue, consider the following strategies:

Using REGEXP_COUNT() for Accurate Splitting

The modified query introduces REGEXP_COUNT() to determine the number of comma-separated values in each row:

WITH CTE AS (
    SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
    UNION
    SELECT 'f,g' temp, 2 slno FROM DUAL
    UNION
    SELECT 'h' temp, 3 slno FROM DUAL
)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    AND PRIOR slno = slno
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

Leveraging Connection by Level and Prior DBMS_RANDOM.VALUE

This enhanced query utilizes CONNECT BY LEVEL along with PRIOR DBMS_RANDOM.VALUE to eliminate duplicates:

WITH CTE AS (
    SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
    UNION
    SELECT 'f,g' temp, 2 slno FROM DUAL
    UNION
    SELECT 'h' temp, 3 slno FROM DUAL
)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    AND PRIOR slno = slno

By utilizing this modified query, you can effectively split comma-separated values into multiple rows while ensuring that duplicate rows are eliminated.

The above is the detailed content of How to Efficiently Split Comma-Separated Values into Multiple Rows in Oracle Without Duplicates?. For more information, please follow other related articles on the PHP Chinese website!