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

Split comma separated string into multiple lines in Oracle

Splitting a comma-delimited string into multiple rows is a common data preprocessing and analysis task in Oracle Database. While there are many ways to accomplish this, we're going to look at one simple and effective method.

One way is to take advantage of Oracle's regular expressions and connect by functionality. Consider the following table, where each record contains comma-separated errors:

名称	项目	错误
108	test	Err1, Err2, Err3
109	test2	Err1

Our goal is to extract each error into a separate line, resulting in the following:

名称	项目	错误
108	test	Err1
108	test	Err2
108	test	Err3
109	test2	Err1

To do this, we use hierarchical query and regexp_substr to extract each error based on the number of occurrences of the error. The following query demonstrates this approach:

<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 <= length(regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels
order by name</code>

This query uses commas to replace the length of the string (regexp_replace) and a hierarchical query mechanism to create a series of different levels. Each level represents the number of occurrences of errors in a comma-separated string. The regexp_substr function then extracts the errors based on their occurrence count and the final result set contains the desired output.

This method provides a simple and efficient way to split a comma-delimited string into multiple lines in Oracle 10g and later. It leverages Oracle's powerful string manipulation capabilities and hierarchical query capabilities to achieve the required data transformations.

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