如何在 Oracle SQL 中有效地將逗號分隔值轉換為行？

Oracle SQL中逗號分隔值轉行策略

資料庫記錄中以逗號分隔值的形式組織數據，在尋求表格表示時會帶來挑戰。本文探討了多種高效率將此類資料轉換為行的SQL技術，方便資料擷取與操作。

問題：資料庫記錄中的逗號分隔值

考慮以下表格結構，其中value列包含逗號分隔值：

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

目標是將此資料轉換為表格格式，將每個值分隔到各自的行中：

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

方法一：使用REGEXP_SUBSTR和Connect By

一個方法是利用Oracle的REGEXP_SUBSTR和CONNECT BY子句：

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

此方法使用正規表示式(REGEXP_SUBSTR)來擷取每個子字串，而CONNECT BY遞歸地迭代逗號分隔的值。

方法二：使用遞迴聯合的CTE

另一種技術是使用帶有遞歸聯合的公共表表達式(CTE)：

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

此方法使用遞歸CTE將逗號分隔的值分解成單一元素。

方法三：使用INSTR的遞迴方式

第三種解決方案採用帶有INSTR函數的遞歸CTE來識別每個子字串的起始和結束位置：

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

此方法利用INSTR遞歸地決定每個子字串的位置並相應地提取它們。

以上是如何在 Oracle SQL 中有效地將逗號分隔值轉換為行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！