如何在 Oracle 中将逗号分隔的值分隔成行？

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

Oracle 数据库经常将数据存储为单列中的逗号分隔值，这阻碍了高效的数据操作和检索。 本文介绍了几种有效地将逗号分隔数据转换为单独行的方法，从而改进数据分析和处理。

方法一：正则表达式递归查询

这个优雅的解决方案使用递归查询和正则表达式来高效提取值：

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

CONNECT BY 子句迭代逗号分隔的值，而 REGEXP_SUBSTR 提取每个子字符串。

方法 2：使用 CTE 的符合 ANSI 的方法

为了增强可移植性，通用表表达式 (CTE) 提供了符合 ANSI 的替代方案：

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

这种递归 CTE 实现了与之前的方法相同的结果。

方法3：不使用正则表达式的递归方法

第三个选项避免使用正则表达式，仅依赖于字符串操作：

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

此方法利用 INSTR 函数查找逗号位置，并利用 SUBSTR 提取值。

这些技术提供了高效可靠的解决方案，用于将逗号分隔值转换为 Oracle 数据库中的行，从而促进改进数据处理和分析。

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