Oracle split with commas (,), column and column Conversion

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

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

2. For '1, 2, 3, 4, 5 '(the comma is in the string)

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','[^,------------------------------12345

3. Use 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_splitIS  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 ',', '); -- the second single quotation mark is the character to be separated from the previous string
COLUMN_VALUE--------------------------------------------------------------------------------12345
SQL> select * from table (fn_split ('1, 2, 3, 4. 5 ','. '));
COLUMN_VALUE--------------------------------------------------------------------------------1, 2, 3, 45
SQL>

Refer:

Http://www.itpub.net/thread-1346178-1-1.html

Many of you have summarized several methods of column-and-column conversion. Today I found a new method () to share with you.
1. SYS. odcivarchar2list:
Select column_value from table (SYS. odcivarchar2list ('1', '2', '3', '4', '5 '));
Column_value
--------------------------------------------------------------------------------
1
2
3
4
5
Oracle versions above 10 Gb support SYS. odcivarchar2list. In fact, SYS. odcivarchar2list is only a type,
Therefore, in 9i, you can create a type to use this function:
Create or replace type my_odcivarchar2list as varray (32767) of varchar2 (4000 );

Select column_value from table (my_odcivarchar2list ('1', '2', '3', '4', '5 '));
Column_value
--------------------------------------------------------------------------------
1
2
3
4
5
However, when '1', '2', '3', '4', '5' is used as a string ('1, '), there is no way to convert it:
Select column_value from table (my_odcivarchar2list ('1, 2, 3, 4, 5 '));
Column_value
--------------------------------------------------------------------------------
1, 2, 3, 4, 5

Conclusion: (1) the table function queries the content in the array using SQL statements;
(2) odcivarchar2list can be used in 9i and later versions. In 9i, you can directly use SYS. odcivarchar2list by creating type, 10 Gb or above;
(3) odcivarchar2list is applicable to character sets and does not apply to a single character string. If it is a single character string, you can implement it by referring to the (below) method in section 2.

You are welcome to discuss and propose more and better methods ~~

See ----------------------------------------------------------------
2. Convert columns into rows using other methods (this is just for your reference)
(1) connect by (using 9i, 10g, 11g)
With T as (select '1, 2, 3, 4, 5 'as STR from dual)
Select str1
From (select distinct
Substr (T. ca, instr (T. ca, ',', 1, C. lv) + 1,
Instr (T. CA, ',', 1, C. LV + 1)-(instr (T. CA, ',', 1, C. LV) + 1) as str1
From (select ',' | STR | ',' as Ca, length (STR | ',')-nvl (length (replace (STR ,', '), 0) as CNT from T) T,
(Select level LV from dual connect by level <= 9) c
Where C. lv <= T. CNT
Order by str1 );
(2). Regular Expression (10 Gb or above)
With test as (select '1, 2, 3, 4, 5 'as STR from dual)
Select distinct regexp_substr (STR, '[^,] +', 1, level)
From Test
Connect by rownum <= 5;