Oracle以逗號分隔的字符串拆分為多行數據實例詳解
RACLE開發知識：Oracle以逗號分隔的字符串拆分為多行數據實例詳解
前言
近期在工作中遇到某表某字段是可擴展數據內容，信息以逗號分隔生成的，現需求要根據此字段數據在其它表查詢相關的內容展現出來，第一想法是切割數據，以逗號作為切割符，以下為總結的實現方法，以供大家參考、指教。
1、regexp_substr函數，通過正則來拆分字符串，函數用法為:(必須是oracle 10g+的版本才支持）
REGEXP_SUBSTR函數格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要進行正則處理的字符串
__pattern ：進行匹配的正則表達式
__position ：起始位置，從第幾個字符開始正則表達式匹配（默認為1）
__occurrence ：獲取第幾個分割出來的組（分割后最初的字符串會按分割的順序排列成組），默認為1
__modifier ：模式（’i’不區分大小寫進行檢索；’c’區分大小寫進行檢索。默認為’c’。）針對的是正則表達式里字符大小寫的匹配
——————————————————————————————————————-
此函數只能每次取一個字符串出來，有點雞肋，字符串中逗號的數量是不確定的，如果有2個逗號，需要提取的字段就是3個。為了確定有多少個需要提取的字段，需要用到connect by命令實現動態參數構造連續的值，通過原字符串長度和被替換后字符串長度相減，可以得到原字符串中的逗號數量，加1后得到需要提取的匹配字段數量。
SQL:
select bs from cs1_0 where slid=’201804100038′
–正則分割后的第一個值
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid=’201804100038′),'[^,]+’,1,1,’i’) as 分割后結果 FROM DUAL;
–獲取一個多個數值的列，從而能夠讓結果以多行的形式展示出來
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
–將上面REGEXP_SUBSTR的occurrence（標識第幾個匹配組）實現動態參數，使用 connect by組合起來
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid=’201804100038′),'[^,]+’,1,LEVEL,’i’) as 分割后結果 FROM DUAL CONNECT BY LEVEL <=5;
–優化一下（動態獲匹配組標識行數）
select regexp_substr((select bs from cs1_0 where slid=’201804100038′),'[^,]+’,1,LEVEL,’i’) as 分割后結果 from dual
connect by level <= length((select bs from cs1_0 where slid=’201804100038′))-length(regexp_replace((select bs from cs1_0 where slid=’201804100038′),’,’,”))+1;
2、以Type類型和function函數的方式實現
1）建立TYPE類型
CREATE OR REPLACE TYPE strsplit_type_12 IS TABLE OF VARCHAR2 (4000)
2）建立function存儲函數
create or replace function strsplit_66(p_value varchar2,p_split varchar2 ) –字符串，切割符
–根據特定字符來切割字符串
return strsplit_type_12
pipelined is
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;
begin
loop
v_idx := instr(v_strs_last, p_split);
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx – 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;
end strsplit_66;
SELECT ROWNUM 序號, a.* FROM TABLE(strsplit_66((select bs from cs1_0 where slid=’201804100038′), ‘,’)) a;
測試一下：
浙公網安備 33010602011771號