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

Splitting Multiple Comma-Separated Values in Oracle Table to Multiple Rows without Duplicates

When dealing with data in Oracle tables, it is often necessary to split comma-separated values into multiple rows. Using a combination of CONNECT BY and regular expressions, it is possible to achieve this. However, it is essential to avoid duplicate rows in the query result.

Query for Splitting Comma-Separated Values

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

This query employs a recursive WITH clause to create a Common Table Expression (CTE) named CTE. The CTE comprises three rows of data with comma-separated values.

The main SELECT statement then extracts the individual values from the temp column. The TRIM function is used to remove any leading or trailing whitespace from the values. The REGEXP_SUBSTR() function is responsible for splitting the temp column into separate values based on the comma delimiter.

The CONNECT BY clause performs the recursive splitting. It ensures that each comma-separated value is assigned a unique level. The PRIOR slno = slno condition ensures that the level is incremented only within each row (slno).

Additionally, the PRIOR DBMS_RANDOM.VALUE IS NOT NULL condition is used to prevent duplicate rows from being generated. DBMS_RANDOM.VALUE is a pseudocolumn that returns a random number. By comparing it to IS NOT NULL, we introduce a random element that eliminates duplicates.

The final result is a table with multiple rows, each containing a single value from the original comma-separated string. This approach effectively splits the values without producing any duplicate rows.

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