Oracleのカンマ区切り文字列を複数行に分割したデータ例

select bs from cs1_0 where slid='201804100038'
-- The first value after regular splitting
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,1,'i') as the result after splitting FROM DUAL;
-- get a column with multiple values so that the results can be displayed in multiple rows 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
--- the above REGEXP_SUBSTR ocurrence (identify the first few matching groups) to achieve dynamic parameters, using the connect by combination
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as split result FROM DUAL CONNECT BY LEVEL <=5;
--optimize it (dynamically get the number of rows with matching group identifiers)
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;

1) Create TYPE type


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

2) Create function stored functions
create or replace function strsplit_66(p_value varchar2,p_split varchar2 ) --string, cut character
--Cut the string according to specific characters
 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 serial number, a.* FROM TABLE(strsplit_66((select bs from cs1_0 where slid='201804100038'), ',')) a;