Oracle Comma Separated String Split into Multiple Lines of Data Example Details

preface

Recently, I encountered in my work that a field in a table is extensible data content, and the information is generated by comma separation. Now we need to show the content related to the query of this field data in other tables. The first idea is to cut data, using comma as the cutting character. The following is a summary of the implementation method for your reference and guidance.

1、regexp_ The substr function is used to split strings through regular rules. The function usage is: (It must be supported by the version of oracle 10g+)

REGEXP_ The format of the SUBSTR function is as follows:

function REGEXP_ SUBSTR(String, pattern, position, occurrence, modifier)

__ Srcstr: the string that needs regular processing

__ Pattern: regular expression for matching

__ Position: start position, start regular expression matching from the first character (default is 1)

__ Occurrence: Get the first split group (after splitting, the initial string will be arranged into groups according to the splitting order), and the default value is 1

__ Modifier: Pattern ('i' is case insensitive for retrieval; 'c' is case sensitive for retrieval. The default is' c '.) It is for matching the case of characters in regular expressions

This function can only fetch one string at a time, which is a bit lame. The number of commas in the string is uncertain. If there are two commas, three fields need to be extracted. To determine how many fields need to be extracted, the connect by command is used to construct continuous values for dynamic parameters. The number of commas in the original string can be obtained by subtracting the length of the original string from the length of the replaced string, and the number of matching fields to be extracted can be obtained by adding 1.

SQL:

 select bs from cs1_ 0 where slid='201804100038' --First value after regular segmentation SELECT REGEXP_ SUBSTR ((select bs from cs1_0 where slid='201804100038'), '[^,]+', 1,1, 'i') as the split result 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; --REGEXP_ The occurrence of SUBSTR (identifying the first matching group) implements dynamic parameters, and uses connect by combination SELECT REGEXP_ SUBSTR ((select bs from cs1_0 where slid='201804100038'), '[^,]+', 1, LEVEL, 'i') as the split result FROM DUAL CONNECT BY LEVEL<=5; --Optimize it (dynamically obtain the number of matching group ID lines) select regexp_ Substr ((select bs from cs1_0 where slid='201804100038'), '[^,]+', 1, LEVEL, 'i') as split result 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. Implemented in the form of Type and function function

 1) Create Type CREATE OR REPLACE TYPE strsplit_ type_ 12 IS TABLE OF VARCHAR2 (4000) 2) Create function storage function create or replace function strsplit_ 66 (p_value varchar2, p_split varchar2) -- string, delimiter --Cutting strings according to specific characters return strsplit_ type_ twelve 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 S/N, a. * FROM TABLE (strsplit_66 ((select bs from cs1_0 where slid='201804100038'), ',')) a;