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

Convert comma separated values ​​to rows in Oracle

Comma is a common delimiter to represent multiple values ​​as a single string, but when working with data in Oracle, you may need to separate these values ​​into different rows. This can be achieved in a variety of ways.

Recursive query using regular expressions

One way is to use a recursive query with a regular expression to extract each value:

<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 uses the REGEXP_SUBSTR function to extract each non-comma substring and then concatenates the results using a recursive query.

Recursive query without regular expressions

Another recursive approach that avoids using regular expressions:

<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 query uses the INSTR function to find the position of the comma, and then uses the SUBSTR function to extract the individual values.

CTE using recursive union

The third method uses a common table expression (CTE) with a recursive union:

<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 query uses the UNION ALL operator to create a recursive CTE that extracts every non-comma substring.

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