Oracle以逗號分隔的字符串拆分為多行數(shù)據(jù)實例詳解

更新時間：2021年07月15日 14:38:31 作者：南清風

做多選功能時為了簡便,會在某個字段中存儲多個值,保存時雖然省事,但后續(xù)的查詢統(tǒng)計時還需要拆分數(shù)據(jù)才行,因此這時需要將字段內的值分成多行以便后續(xù)使用,這篇文章主要給大家介紹了關于Oracle以逗號分隔的字符串拆分為多行數(shù)據(jù)的相關資料,需要的朋友可以參考下

前言

近期在工作中遇到某表某字段是可擴展數(shù)據(jù)內容，信息以逗號分隔生成的，現(xiàn)需求要根據(jù)此字段數(shù)據(jù)在其它表查詢相關的內容展現(xiàn)出來，第一想法是切割數(shù)據(jù)，以逗號作為切割符，以下為總結的實現(xiàn)方法，以供大家參考、指教。

1、regexp_substr函數(shù)，通過正則來拆分字符串，函數(shù)用法為:(必須是oracle 10g+的版本才支持）

REGEXP_SUBSTR函數(shù)格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr ：需要進行正則處理的字符串

__pattern ：進行匹配的正則表達式

__position ：起始位置，從第幾個字符開始正則表達式匹配（默認為1）

__occurrence ：獲取第幾個分割出來的組（分割后最初的字符串會按分割的順序排列成組），默認為1

__modifier ：模式（'i'不區(qū)分大小寫進行檢索；'c'區(qū)分大小寫進行檢索。默認為'c'。）針對的是正則表達式里字符大小寫的匹配

-------------------------------------------------------------------------------------------------------------------

此函數(shù)只能每次取一個字符串出來，有點雞肋，字符串中逗號的數(shù)量是不確定的，如果有2個逗號，需要提取的字段就是3個。為了確定有多少個需要提取的字段，需要用到connect by命令實現(xiàn)動態(tài)參數(shù)構造連續(xù)的值，通過原字符串長度和被替換后字符串長度相減，可以得到原字符串中的逗號數(shù)量，加1后得到需要提取的匹配字段數(shù)量。

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;
--獲取一個多個數(shù)值的列，從而能夠讓結果以多行的形式展示出來 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
--將上面REGEXP_SUBSTR的occurrence（標識第幾個匹配組）實現(xiàn)動態(tài)參數(shù)，使用 connect by組合起來
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后結果 FROM DUAL CONNECT BY LEVEL <=5;
--優(yōu)化一下（動態(tài)獲匹配組標識行數(shù)）
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函數(shù)的方式實現(xiàn)

1）建立TYPE類型


CREATE OR REPLACE TYPE strsplit_type_12 IS TABLE OF VARCHAR2 (4000)

2）建立function存儲函數(shù)
create or replace function strsplit_66(p_value varchar2,p_split varchar2 )  --字符串，切割符
--根據(jù)特定字符來切割字符串
 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;

測試一下：