oracle split去逗號，行列轉換

1.針對  '1','2','3','4','5'（逗號在字串外面）

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

2.針對'1,2,3,4,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','[^,------------------------------12345

3.使用函數

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;

測試：

SQL> select * from table(fn_split('1,2,3,4,5',','));    --第二個單引號中是前面字串中需要被分隔的字元
COLUMN_VALUE--------------------------------------------------------------------------------12345
SQL> select * from table(fn_split('1,2,3,4。5','。'));
COLUMN_VALUE--------------------------------------------------------------------------------1,2,3,45
SQL>

參考：

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

眾大牛們已經總結了行列轉換的若干方法。今天發現了一種新的方法( )，和大家分享下。
1.SYS.ODCIVARCHAR2LIST：
SELECT COLUMN_VALUE  FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
1
2
3
4
5
Oracle 10G 以上版本才支援SYS.ODCIVARCHAR2LIST，其實SYS.ODCIVARCHAR2LIST只不過是一個TYPE，
所以在9I版本中可以通過建立一個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
但是，當'1','2','3','4','5'  作為一個字串('1,2,3,4,5')就沒有辦法轉換了：
SELECT COLUMN_VALUE  FROM TABLE(MY_ODCIVARCHAR2LIST('1,2,3,4,5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
1,2,3,4,5

總結：(1)Table函數將數組裡的內容通過SQL語句查詢出來；
      (2)ODCIVARCHAR2LIST 在9I 及以上版本中均可使用。在9I中可通過建立TYPE，10G及以上直接使用SYS.ODCIVARCHAR2LIST；
      (3)ODCIVARCHAR2LIST 適用於字元集，不適用單個字串，如果是單個字串，可以通過參考2中(如下)方法實現。

歡迎大家討論，提出更多更好的方法~~

參考----------------------------------------------------------------
2.其他方法實現列轉行（大牛們早已經總結，僅供參考）
(1) 利用CONNECT BY （使用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).Regex（使用10G及以上版本）
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;