How to Convert Comma-Separated Values to Rows in Oracle?

Transforming Comma-Separated Data in Oracle Databases

Many data manipulation tasks require converting comma-separated values (CSV) within a single column into individual rows for easier analysis. Oracle offers several approaches to accomplish this.

Method 1: Recursive SQL with Regular Expressions

This technique uses recursive SQL queries combined with regular expressions for efficient CSV splitting:

<code class="language-sql">select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
   order by id, level;</code>

This query iteratively extracts each comma-delimited value, generating a new row for each, and includes a level indicator.

Method 2: Recursive SQL (CTE) without Regular Expressions

A more standard SQL approach utilizes a Common Table Expression (CTE):

<code class="language-sql">with t (id,res,val,lev) as (
           select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev
             from tbl1
            where regexp_substr(value, '[^,]+', 1, 1) is not null
            union all           
            select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev
              from t
              where regexp_substr(val, '[^,]+', 1, lev+1) is not null
              )
select id, res,lev
  from t
order by id, lev;</code>

This recursive CTE achieves the same result as the previous method but avoids reliance on regular expressions.

Method 3: Non-Recursive Approach (INSTR and SUBSTR)

A non-recursive alternative uses INSTR() and SUBSTR() functions to locate and extract values:

<code class="language-sql">WITH t ( id, value, start_pos, end_pos ) AS
  ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1
  UNION ALL
  SELECT id,
    value,
    end_pos                    + 1,
    INSTR( value, ',', end_pos + 1 )
  FROM t
  WHERE end_pos > 0
  )
SELECT id,
  SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value
FROM t
ORDER BY id,
  start_pos;</code>

This method iteratively finds comma positions and extracts substrings, offering a different approach to the problem.

The optimal method depends on your specific data and performance needs. Consider testing each approach to determine the most efficient solution for your Oracle environment.

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