How to Avoid Duplicate Rows When Splitting Comma-Separated Values in Oracle?

Splitting Multiple Comma-Separated Values into Multiple Oracle Table Rows without Duplicates

When splitting comma-separated data into multiple rows using Oracle's CONNECT BY and regular expressions, it's possible to encounter duplicate rows. Below is the original query that was generating duplicate results:

WITH CTE AS (
    SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
    UNION
    SELECT 'f,g', 2 from dual
    UNION
    SELECT 'h', 3 FROM DUAL
)

SELECT TRIM(REGEXP_SUBSTR(TEMP, '[^,]+', 1, LEVEL)), SLNO
FROM CTE
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

To resolve the issue of duplicate rows, the following modifications were made to the query:

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

Explanation:

• The REGEXP_COUNT function is used to determine the number of comma-separated values in each string.
• The PRIOR operator ensures that the current row is connected to the previous row based on the slno column.
• The DBMS_RANDOM.VALUE function generates a random value and is used to introduce randomness in the partitioning process, which helps prevent duplicate rows from being generated.

The above is the detailed content of How to Avoid Duplicate Rows When Splitting Comma-Separated Values in Oracle?. For more information, please follow other related articles on the PHP Chinese website!