频道导航

oracle split去逗号，行列转换

2020-06-02 Oracle 前端之家

前端之家收集整理的这篇文章主要介绍了oracle split去逗号，行列转换，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

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

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

[sql]view plaincopy 
   
 sql>SELECTCOLUMN_VALUEFROMTABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
 
 COLUMN_VALUE
 --------------------------------------------------------------------------------
 1
 2
 3
 4
 5

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

copy 
    
   

regexp_substr(

'1,5'

'[^,]+'

,1,rownum)

from

dual

2connectbyrownum<=length('1,5')-length(replace('1,','))+1
3;
REGEXP_SUBSTR('1,'[^,
------------------------------
3.使用函数

copy

CREATEORREPLACETYPEty_str_splitISTABLEOFVARCHAR2(4000);

copy 
    
   

FUNCTION

fn_split(p_str

CLOB,p_delimiter

VARCHAR2)

RETURNty_str_split
IS
jINT:=0;
iINT:=1;
len len1INT:=0;
strVARCHAR2(4000);
str_splitty_str_split:=ty_str_split();
BEGIN
len:=LENGTH(p_str);
len1:=LENGTH(p_delimiter);
WHILEj<len
LOOP
j:=INSTR(p_str,p_delimiter,i);
IFj=0
THEN
j:=len;
str:=SUBSTR(p_str,i);
str_split.EXTEND;
str_split(str_split.COUNT):=str;
IFi>=len
THEN
EXIT;
ENDIF;
ELSE
str:=SUBSTR(p_str,i,j-i);
i:=j+len1;
ENDLOOP;
RETURNstr_split;
ENDfn_split;

测试：

copy 
    
   

sql>select*fromtable(fn_split('1,'));--第二个单引号中是前面字符串中需要被分隔的字符COLUMN_VALUE

5sql>,'。'));COLUMN_VALUE
1,4
5sql>

参考：

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;