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