[Oracle Debug] 實現用regexp_substr分解資料成多個欄位


Posted by Vanchy on 2022-06-17

* 目標

給定資料如下:將此字串分割成三個欄位

SELECT '1^^2' AS temp_value
FROM dual

* 使用regexp_substr (方法1)

WITH temp AS
(
    SELECT '1^2^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 2 3

* 使用regexp_substr (方法2)

當遇到有「空值」的時候,方法1會造成資料和欄位錯置

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

* 使用regexp_substr (方法2 改良)

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1, null, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2, null, 1) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3, null, 1) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

#oracle #SQL







Related Posts

【Day03】註冊免費 DNS

【Day03】註冊免費 DNS

CSS BEM命名

CSS BEM命名

Command Line Interface (CLI) 超入門

Command Line Interface (CLI) 超入門


Comments