How Can I Efficiently Split Comma-Separated Values in Oracle Without Generating Duplicate Rows?

Splitting Multiple Comma-Separated Values in Oracle Table to Unique Rows

In oracle, splitting comma-separated values into multiple rows is commonly done using regular expressions and the CONNECT BY clause. However, one challenge often encountered is the generation of duplicate rows. This can be especially problematic in tables with multiple rows where each row contains comma-separated strings.

To address this issue, a modified query can be used to split the values while effectively eliminating duplicate rows:

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

This query introduces two key modifications:

1. Prior Row Check: The PRIOR slno = slno condition ensures that only rows from the same source row are processed, effectively preventing duplicate rows from different rows.
2. Random Value Check: The PRIOR DBMS_RANDOM.VALUE IS NOT NULL condition selects a random value for each row, ensuring that every split value has a unique combination of row and random value.

As a result, this query effectively splits comma-separated values into multiple rows while maintaining the unique row identification and preventing duplicate rows from being generated.

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