How Can I Efficiently Split Strings into Multiple Rows in Oracle?

Efficiently Handling String Splits into Multiple Rows in Oracle Databases

Database operations often require splitting strings into individual rows. Oracle offers effective methods, especially in versions 10g and later, to manage this task.

A Basic Method Using Regular Expressions

A simple yet powerful technique employs regular expressions and the connect by clause. This involves constructing a temporary table and using regular expression functions to extract substrings:

with temp as (
    select 108 Name, 'test' Project, 'Err1, Err2, Err3' 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 sys.OdciNumberList)) levels;

Understanding the Code

1. The temp CTE (Common Table Expression) simulates sample data.
2. length(regexp_replace(t.error, '[^,] ')) 1 determines the number of errors by counting comma-separated values.
3. select level from dual connect by level generates a sequence of numbers from 1 to the error count.
4. table(cast(multiset(...) as sys.OdciNumberList)) transforms the number sequence into a usable collection.
5. The cross join combines temp with the number collection, creating all necessary combinations.
6. trim(regexp_substr(t.error, '[^,] ', 1, levels.column_value)) extracts each error using the generated number as the position indicator.

This method efficiently splits strings with multiple values into separate rows while maintaining data integrity.

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