如何在 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中文网其他相关文章！