How Can I Split a Comma-Delimited String into Multiple Rows in Oracle 10g and 11g?

Efficiently Splitting Comma-Separated Strings into Rows in Oracle 10g and 11g

Oracle offers several methods for splitting comma-delimited strings into individual rows. This improved approach leverages regular expressions and the CONNECT BY clause for a more efficient solution:

WITH temp AS (
    SELECT 108 AS Name, 'test' AS Project, 'Err1, Err2, Err3' AS Error FROM DUAL
    UNION ALL
    SELECT 109, 'test2', 'Err1' FROM DUAL
)
SELECT DISTINCT
  t.name, t.project,
  TRIM(REGEXP_SUBSTR(t.error, '[^,]+', 1, levels.column_value)) AS error
FROM
  temp t,
  TABLE(CAST(MULTISET(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(t.error, '[^,]+')) + 1) AS OdciNumberList)) levels

Detailed Explanation:

This query uses a hierarchical query to achieve the string splitting. Let's break down the steps:

1. Sample Data: The WITH clause defines a sample table (temp) containing comma-separated error strings.

2. Delimiter Count: LENGTH(REGEXP_REPLACE(t.error, '[^,] ')) 1 calculates the number of commas plus one (to account for the last element). This determines the number of rows needed.

3. Generating the Sequence: SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= ... generates a sequence of numbers from 1 up to the count of elements. CONNECT BY is crucial for creating this sequence.

4. Collection Creation: MULTISET(...) creates a collection (a set of numbers) from the generated sequence. CAST(... AS OdciNumberList) converts this into an Oracle collection type.

5. Table Transformation: TABLE(...) transforms the collection into a result set, allowing us to join it with the temp table.

6. String Extraction: REGEXP_SUBSTR(t.error, '[^,] ', 1, levels.column_value) extracts each comma-separated substring. [^,] is a regular expression matching one or more non-comma characters. levels.column_value provides the occurrence number for the extraction.

7. Trimming and Deduplication: TRIM(...) removes leading/trailing spaces. SELECT DISTINCT eliminates duplicate rows, ensuring each error appears only once.

This approach is efficient because it avoids loops and utilizes Oracle's built-in functions for optimized performance. The regular expression provides a robust method for handling varied string lengths and potential irregularities in the comma-delimited data.

The above is the detailed content of How Can I Split a Comma-Delimited String into Multiple Rows in Oracle 10g and 11g?. For more information, please follow other related articles on the PHP Chinese website!