How Can I Efficiently Convert Comma-Separated Values into Rows in Oracle SQL?

Comma separated value conversion strategy in Oracle SQL

Organizing data in database records as comma-separated values ​​creates challenges when seeking tabular representation. This article explores several SQL techniques for efficiently converting such data into rows for easy data extraction and manipulation.

Issue: Comma separated values ​​in database records

Consider the following table structure, where the value column contains comma separated values:

<code>CREATE TABLE tbl1 (
  id NUMBER,
  value VARCHAR2(50)
);

INSERT INTO tbl1 VALUES (1, 'AA, UT, BT, SK, SX');
INSERT INTO tbl1 VALUES (2, 'AA, UT, SX');
INSERT INTO tbl1 VALUES (3, 'UT, SK, SX, ZF');</code>

The goal is to convert this data into a tabular format, with each value separated into its own row:

<code>ID | VALUE
-------------
1  | AA
1  | UT
1  | BT
1  | SK
1  | SX
2  | AA
2  | UT
2  | SX
3  | UT
3  | SK
3  | SX
3  | ZF</code>

Method 1: Use REGEXP_SUBSTR and Connect By

One way is to take advantage of Oracle's REGEXP_SUBSTR and CONNECT BY clause:

<code>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 method uses a regular expression (REGEXP_SUBSTR) to extract each substring, while CONNECT BY iterates over the comma separated values ​​recursively.

Method 2: CTE using recursive union

Another technique is to use a common table expression (CTE) with a recursive union:

<code>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 method uses a recursive CTE to break comma separated values ​​into individual elements.

Method 3: Using the recursive method of INSTR

The third solution uses a recursive CTE with the INSTR function to identify the start and end position of each substring:

<code>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 utilizes INSTR to recursively determine the position of each substring and extract them accordingly.

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