If a string is separated by commas or other symbols, you want to divide it into columns, such as' first field, second field, third field ',
Dismantle

first field
second field
third field

1. For '1', '2', '3', '4', '5' (comma outside string):

SQL> SELECT COLUMN_VALUE  FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
1
2
3
4
5

2. For '1,2,3,4,5' (comma in string) (regular expression supported starting with 10G):

SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual
  2  connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1
  3  ;
 
REGEXP_SUBSTR('1,2,3,4,5','[^,
------------------------------
1
2
3
4
5

TIP:
The REGEXP_SUBSTR function is to intercept the string starting with regular instead of (comma). The second parameter is to take the second group, rownum pseudo column sequence number, connect loop, the number of cycles is the total length of the string - after removing the separator = several separators + 1

3. Using functions

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2)
 RETURN ty_str_split
IS
  j INT := 0;
  i INT := 1;
  len INT := 0;
  len1 INT := 0;
  str VARCHAR2 (4000);
  str_split ty_str_split := ty_str_split ();
BEGIN
  len := LENGTH (p_str);
  len1 := LENGTH (p_delimiter);
 
  WHILE j < len
  LOOP
    j := INSTR (p_str, p_delimiter, i);
 
    IF j = 0
    THEN
        j := len;
        str := SUBSTR (p_str, i);
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;
 
        IF i >= len
        THEN
          EXIT;
        END IF;
    ELSE
        str := SUBSTR (p_str, i, j - i);
        i := j + len1;
        str_split.EXTEND;
        str_split (str_split.COUNT) := str;
    END IF;
  END LOOP;
 
  RETURN str_split;
END fn_split;

Test:

SQL> select * from table(fn_split('1,2,3,4,5',',')); --In the second single quotation mark are the characters that need to be separated in the preceding string

COLUMN_VALUE ----------------------------------- 1 2 3 4 5

SQL> select * from table(fn_split('1,2,3,4. 5','. '));

COLUMN_VALUE ------------------------------------ 1,2,3,4 5

Turn from https://blog.csdn.net/wanglilin/article/details/7231712

Related articles: http://www.anbob.com/archives/221.html

                 https://blog.csdn.net/mchdba/article/details/51946573