1.針對 '1','2','3','4','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> 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.使用函數
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;
測試:
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
參考:
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).正規表達式(使用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;
轉載自:http://blog.csdn.net/wanglilin/article/details/7231712