oracle split去逗号，行列转换

用10G开始支持的正则表达式

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

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

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

selectregexp_substr('1,5','[^,]+',1,rownum)fromdual
1. 2connectbyrownum<=length('1,5')-length(replace('1,','))+1
2. 3;
3. REGEXP_SUBSTR('1,'[^,
4. ------------------------------
5. 
   3.使用函数
   copy
   CREATEORREPLACETYPEty_str_splitISTABLEOFVARCHAR2(4000);
   copy
   REPLACEFUNCTIONfn_split(p_strINCLOB,p_delimiterINVARCHAR2)
   1. RETURNty_str_split
   2. IS
   3. jINT:=0;
   4. iINT:=1;
   5. len len1INT:=0;
   6. strVARCHAR2(4000);
   7. str_splitty_str_split:=ty_str_split();
   8. BEGIN
   9. len:=LENGTH(p_str);
   10. len1:=LENGTH(p_delimiter);
   12. WHILEj<len
   13. LOOP
   14. j:=INSTR(p_str,p_delimiter,i);
   15. IFj=0
   16. THEN
   17. j:=len;
   18. str:=SUBSTR(p_str,i);
   19. str_split.EXTEND;
   20. str_split(str_split.COUNT):=str;
   21. IFi>=len
   22. THEN
   23. EXIT;
   24. ENDIF;
   25. ELSE
   26. str:=SUBSTR(p_str,i,j-i);
   27. i:=j+len1;
   28. ENDLOOP;
   29. RETURNstr_split;
   30. ENDfn_split;

   
   测试：

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

   
   参考：

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

   众大牛们已经总结了行列转换的若干方法。今天发现了一种新的方法( )，和大家分享下。
   1.SYS.ODCIVARCHAR2LIST：
   SELECT COLUMN_VALUEFROM TABLE(SYS.ODCIVARCHAR2LIST('1','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_VALUEFROM TABLE(MY_ODCIVARCHAR2LIST('1',51); font-family:Arial; font-size:14px; line-height:26px">但是，当'1','5'作为一个字符串('1,5')就没有办法转换了：
   SELECT COLUMN_VALUEFROM TABLE(MY_ODCIVARCHAR2LIST('1,5'));
   1,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,5' AS STR FROM DUAL)
   SELECTSTR1
   FROM ( SELECTDISTINCT
   SUBSTR(T.CA,INSTR(T.CA,',C.LV) + 1,
   INSTR(T.CA,C.LV + 1) -(INSTR(T.CA,C.LV) + 1)) AS STR1
   FROM (SELECT ',' || STR || ',' AS CA,LENGTH(STR || ',') -NVL(LENGTH(REPLACE(STR,')),0) AS CNT FROM T) T,51); font-family:Arial; font-size:14px; line-height:26px"> (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,51); font-family:Arial; font-size:14px; line-height:26px">SELECT DISTINCT REGEXP_SUBSTR(STR,]+',LEVEL)
   FROM TEST
   CONNECT BY ROWNUM <= 5;

   （编辑：李大同）

   【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!