Oracle split to comma, row and column conversions

 

Reproduced

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

1. sql> SELECT column_value from TABLE (SYS.  Odcivarchar2list (' 1 ',' 2 ',' 3 ',' 4 ',' 5 '));
3. Column_value
4. --------------------------------------------------------------------------------
5. 1
6. 2
7. 3
8. 4
9. 5

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

 

1. sql> select regexp_substr ( Span class= "string" > ' 1,2,3,4,5 ', "[^,]+ ', 1,rownum)  from  dual  
2.   2  connect  by rownum<=length ( ' 1,2,3,4,5 ')-length ( replace ( ' 1,2,3,4,5 ',
3. &NBSP;&NBSP;3&NBSP;&NBSP;;&NBSP;&NBSP;
4.    
5. regexp_substr (
6. span class= "comment" >------------------------------  
7. 1&NBSP;&NBSP;
8. 2  
9. 3&NBSP;&NBSP;
10. 4  
11. 5&NBSP;&NBSP;

3. Using functions

1. CREATE OR REPLACE TYPE ty_str_split is TABLE of VARCHAR2 (4000);

2. CREATE OR REPLACE FUNCTION fn_split (p_str in CLOB, p_delimiter in VARCHAR2)
4. RETURN Ty_str_split
6. Is
8. J INT: = 0;
10. I INT: = 1;
12. Len INT: = 0;
14. Len1 INT: = 0;
16. STR VARCHAR2 (4000);
18. Str_split Ty_str_split: = Ty_str_split ();
20. BEGIN
22. Len: = LENGTH (P_STR);
24. Len1: = LENGTH (P_delimiter);
28. While J < Len
30. LOOP
32. J: = INSTR (P_str, P_delimiter, i);
36. IF j = 0
38. Then
40. J: = Len;
42. str: = SUBSTR (P_str, i);
44. Str_split. EXTEND;
46. Str_split (str_split.   COUNT): = str;
50. IF I >= len
52. Then
54. EXIT;
56. END IF;
58. ELSE
60. str: = SUBSTR (P_str, I, j-i);
62. I: = j + len1;
64. Str_split. EXTEND;
66. Str_split (str_split.   COUNT): = str;
68. END IF;
70. END LOOP;
74. RETURN Str_split;
76. END Fn_split;

Test:

2. <p>SQL> SELECT * from table (Fn_split (' 1,2,3,4,5 ',', ')); --The second single quotation mark is the character that needs to be delimited in the preceding string </p><p>column_value
4. --------------------------------------------------------------------------------
6. 1
8. 2
10. 3
12. 4
14. 5</p><p>sql> SELECT * from table (Fn_split (' 1,2,3,4. 5 ','. ')); </p><p>column_value
16. --------------------------------------------------------------------------------
18. 1,2,3,4
20. 5</p><p>sql></p>

Reference:

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

Many of them have summed up a number of ways to change ranks. Today found a new method (), and share it with everyone.
1.SYS. Odcivarchar2list:
SELECT Column_value from TABLE (SYS. Odcivarchar2list (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 '));
Column_value
--------------------------------------------------------------------------------
1
2
3
4
5
Oracle 10G and above only support sys.odcivarchar2list, in fact, sys.odcivarchar2list is just a type,
So in the 9I version, you can use this feature by creating a type:
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 ' as a string (' 1,2,3,4,5 ') there is no way to convert:
SELECT Column_value from TABLE (my_odcivarchar2list (' 1,2,3,4,5 '));
Column_value
--------------------------------------------------------------------------------
1,2,3,4,5

Summary: (1) The table function queries the contents of the array through SQL statements;
(2) Odcivarchar2list can be used in versions 9I and above. The sys.odcivarchar2list can be directly used in 9I by creating type,10g and above;
(3) Odcivarchar2list applies to character sets, does not apply to a single string, and if it is a single string, it can be implemented by referring to the 2 (below) method.

Welcome to discuss, put forward more and better ways ~ ~

Reference----------------------------------------------------------------
2. Other ways to make a career change (Daniel has long been summed up, for reference only)
(1) using 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 to level <= 9) C
WHERE c.lv <= t.cnt
ORDER by STR1);
(2). Regular expressions (using 10G and later)
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;

Oracle split to comma, row and column conversions