如何在 Oracle 中将逗号分隔值转换为行？

转换 Oracle 数据库中的逗号分隔数据

许多数据操作任务需要将单列中的逗号分隔值 (CSV) 转换为单独的行，以便于分析。 Oracle 提供了多种方法来实现此目的。

方法一：使用正则表达式递归SQL

该技术使用递归 SQL 查询结合正则表达式来实现高效的 CSV 分割：

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;

此查询迭代地提取每个逗号分隔的值，为每个值生成一个新行，并包含一个级别指示器。

方法 2：不使用正则表达式的递归 SQL (CTE)

更标准的 SQL 方法利用通用表表达式 (CTE)：

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;

这种递归 CTE 实现了与之前方法相同的结果，但避免了对正则表达式的依赖。

方法 3：非递归方法（INSTR 和 SUBSTR）

非递归替代方案使用 INSTR() 和 SUBSTR() 函数来定位和提取值：

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;

此方法迭代地查找逗号位置并提取子字符串，为解决问题提供了不同的方法。

最佳方法取决于您的具体数据和性能需求。 考虑测试每种方法以确定最适合您的 Oracle 环境的解决方案。

以上是如何在 Oracle 中将逗号分隔值转换为行？的详细内容。更多信息请关注PHP中文网其他相关文章！