oracle split去逗号，行列转换

1.针对  '1','2','3','4','5'（逗号在字符串外面）

[sql] view plain copy print ?

1. SQL> SELECT COLUMN_VALUE  FROMTABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5')); 
2.  
3. COLUMN_VALUE 
4. -------------------------------------------------------------------------------- 
5. 1 
6. 2 
7. 3 
8. 4 
9. 5 

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

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

2.针对'1,2,3,4,5'（逗号在字符串里面）

[sql] view plain copy print ?

1. SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual 
2.   2  connectby rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1 
3.   3  ; 
4.  
5. REGEXP_SUBSTR('1,2,3,4,5','[^, 
6. ------------------------------ 
7. 1 
8. 2 
9. 3 
10. 4 
11. 5 

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

3.使用函数

[sql] view plain copy print ?

1. CREATEORREPLACE TYPE ty_str_split ISTABLEOF VARCHAR2 (4000); 

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

 

[sql] view plain copy print ?

1. CREATEORREPLACEFUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2) 
2. RETURN ty_str_split 
3. IS 
4.   j INT := 0; 
5.   i INT := 1; 
6.   len INT := 0; 
7.   len1 INT := 0; 
8.   str VARCHAR2 (4000); 
9.   str_split ty_str_split := ty_str_split (); 
10. BEGIN 
11.   len := LENGTH (p_str); 
12.   len1 := LENGTH (p_delimiter); 
13.  
14.   WHILE j < len 
15.   LOOP 
16.     j := INSTR (p_str, p_delimiter, i); 
17.  
18.     IF j = 0 
19.     THEN 
20.         j := len; 
21.         str := SUBSTR (p_str, i); 
22.         str_split.EXTEND; 
23.         str_split (str_split.COUNT) := str; 
24.  
25.         IF i >= len 
26.         THEN 
27.           EXIT; 
28.         END IF; 
29.     ELSE 
30.         str := SUBSTR (p_str, i, j - i); 
31.         i := j + len1; 
32.         str_split.EXTEND; 
33.         str_split (str_split.COUNT) := str; 
34.     END IF; 
35.   END LOOP; 
36.  
37.   RETURN str_split; 
38. END fn_split; 

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;

测试：

[sql] view plain copy print ?

1. <p>SQL> select * fromtable(fn_split('1,2,3,4,5',','));    --第二个单引号中是前面字符串中需要被分隔的字符</p><p>COLUMN_VALUE 
2. -------------------------------------------------------------------------------- 
3. 1 
4. 2 
5. 3 
6. 4 
7. 5</p><p>SQL> select * fromtable(fn_split('1,2,3,4。5','。'));</p><p>COLUMN_VALUE 
8. -------------------------------------------------------------------------------- 
9. 1,2,3,4 
10. 5</p><p>SQL></p> 

SQL> select * from table(fn_split('1,2,3,4,5',',')); --第二个单引号中是前面字符串中需要被分隔的字符

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

SQL>