How to Split Comma-Delimited Strings into Multiple Rows in Oracle?

Split string into multiple rows in Oracle database

Introduction

Splitting a comma-separated string into multiple lines is a common task in data processing. In Oracle 10g and 11g, there are multiple ways to achieve this goal efficiently.

Multiple column split

To split a string containing multiple values ​​into multiple columns, you can use a combination of REGEXP_REPLACE, REGEXP_SUBSTR and hierarchical queries.

Query:

WITH temp AS (
    SELECT name, project, error FROM your_table
)
SELECT
    name,
    project,
    TRIM(REGEXP_SUBSTR(error, '[^,]+', 1, levels.column_value)) AS new_error
FROM
    temp,
    TABLE(CAST(
        MULTISET(
            SELECT level FROM dual CONNECT BY level <= REGEXP_COUNT(error, ',') + 1
        ) AS sys.odcinumberlist
    )) levels
WHERE levels.column_value <= REGEXP_COUNT(error, ',') + 1;

Instructions:

1. The REGEXP_SUBSTR function extracts the specified number of occurrences of the specified pattern from a string. The column_value in the hierarchical query determines the nth occurrence.
2. The REGEXP_REPLACE function removes non-delimiter characters to count the total number of errors. (This step is implicit in REGEXP_COUNT in the optimized query)
3. The multiset and TABLE functions create an increasing set of values ​​for hierarchical queries.
4. The cross join with the temp table repeats for each row for each error occurrence.

Conclusion

This improved method provides a flexible way to split multi-column strings into multiple rows in Oracle 10g and 11g. It uses Oracle's built-in functions and advanced techniques such as hierarchical queries to efficiently handle complex string manipulation tasks.

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