如何在 Oracle 中將逗號分隔的值分隔成行？

轉換 Oracle 資料庫中的逗號分隔資料

Oracle 資料庫經常將資料儲存為單列中的逗號分隔值，這阻礙了高效率的資料操作和檢索。 本文介紹了幾種有效地將逗號分隔資料轉換為單獨行的方法，從而改善資料分析和處理。

方法一：正規表示式遞迴查詢

這個優雅的解決方案使用遞歸查詢和正規表示式來高效提取值：

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;

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

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

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

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：不使用正規表示式的遞迴方法

第三個選項避免使用正規表示式，僅依賴字串運算：

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;

此方法利用 INSTR 函數找出逗號位置，並利用 SUBSTR 擷取值。

這些技術提供了高效可靠的解決方案，用於將逗號分隔值轉換為 Oracle 資料庫中的行，從而促進改進資料處理和分析。

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