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 the data, using comma as the cutting character. The following is a summary of the implementation method for your reference and guidance.

1. The regexp_substr function is used to split the string by regularization. The function usage is: (It must be supported by the version of Oracle 10g+)

The REGEXP_SUBSTR function format 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 result after segmentation 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; --Implement dynamic parameters by using the occurrence of REGEXP_SUBSTR above (identifying the first matching group), and combine them with connect by SELECT REGEXP_SUBSTR ((select bs from cs1_0 where slid='201804100038'), '[^,]+', 1, LEVEL, 'i') as the result after segmentation 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 the result after segmentation 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, cutter --Cutting strings 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 S/N, a. * FROM TABLE (strsplit_66 ((select bs from cs1_0 where slid='201804100038'), ',')) a;