How Can I Separate Comma-Delimited Values into Rows in Oracle?

Transforming Comma-Delimited Data in Oracle Databases

Oracle databases frequently store data as comma-separated values within a single column, hindering efficient data manipulation and retrieval. This article presents several methods to effectively convert this comma-delimited data into individual rows, improving data analysis and processing.

Method 1: Recursive Query with Regular Expressions

This elegant solution uses a recursive query and regular expressions for efficient value extraction:

<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>

The CONNECT BY clause iterates through the comma-separated values, while REGEXP_SUBSTR extracts each substring.

Method 2: ANSI-Compliant Approach with CTE

For enhanced portability, a Common Table Expression (CTE) offers an ANSI-compliant alternative:

<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 outcome as the previous method.

Method 3: Recursive Approach without Regular Expressions

A third option avoids regular expressions, relying solely on string manipulation:

<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 leverages the INSTR function to find comma positions and SUBSTR to extract the values.

These techniques offer efficient and reliable solutions for transforming comma-separated values into rows within an Oracle database, facilitating improved data handling and analysis.

The above is the detailed content of How Can I Separate Comma-Delimited Values into Rows in Oracle?. For more information, please follow other related articles on the PHP Chinese website!