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

Split comma separated string to multiple lines in Oracle

In Oracle, you can split a comma-delimited string into multiple lines using a combination of regular expressions and hierarchical queries. Here's a complete guide on how to achieve this:

Problem Statement:

Suppose there is a table with the following structure:

<code>名称 | 项目 | 错误
--------------
108  | test    | Err1, Err2, Err3
109  | test2   | Err1</code>

You want to convert the "error" column into multiple rows, the result is as follows:

<code>名称 | 项目 | 错误
--------------
108  | test    | Err1
108  | test    | Err2
108  | test    | Err3
109  | test2   | Err1</code>

Solution:

<code class="language-sql">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 <= regexp_count(t.error, ',')) as OdciNumberList)) levels
;</code>

Instructions:

1. Create temporary table: Create TEMP table to store the sample data.
2. Use a hierarchical query: The subquery select level from dual connect by level <= regexp_count(t.error, ',') generates a sequence of numbers whose length is equal to the number of commas in the string plus one (i.e. the number of errors).
3. Casts and tables: The table() and cast() functions convert a hierarchical query into a collection of numeric lists and then into a table. This will produce rows with increasing numbers corresponding to each row in the TEMP table.
4. Extract error value: The regexp_substr() function is used to extract a single error value based on the number from the previous step. trim() function removes any leading or trailing whitespace.
5. Join and Select: The main query joins the TEMP table with the table created in step 4 and selects the corresponding columns: Name, Project, and Error.

Note: This solution uses the OdciNumberList type and may require specifying compatibility settings if using an Oracle version earlier than 12c.

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