首頁猿問將逗號(hào)分隔為Oracle中的列

將逗號(hào)分隔為Oracle中的列

Oracle MySQL SQL Server

白板的微信 2019-06-18 17:17:36

將逗號(hào)分隔為Oracle中的列返回值時(shí)使用255個(gè)逗號(hào)分隔的值。是否有一種不需要255個(gè)Substr就可以將這些列分割成列的簡單方法？ROW | VAL----------- 1 | 1.25, 3.87, 2, ... 2 | 5, 4, 3.3, ....到ROW | VAL | VAL | VAL ...--------------------- 1 |1.25 |3.87 | 2 ... 2 | 5 | 4 | 3.3 ...

查看完整描述

3 回答

回首憶惘然

TA貢獻(xiàn)1847條經(jīng)驗(yàn) 獲得超11個(gè)贊

你可以用regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1, 
       regexp_substr(val, '[^,]+', 1, 2) as val2, 
       regexp_substr(val, '[^,]+', 1, 3) as val3, 
       . . .

我建議您在Excel(或其他電子表格)中生成255個(gè)數(shù)字的列，并使用電子表格生成SQL代碼。

反對回復(fù) 2019-06-18

慕神8447489

TA貢獻(xiàn)1780條經(jīng)驗(yàn) 獲得超1個(gè)贊

小心！格式的regexp_substr表達(dá)式'[^,]+'如果列表中有一個(gè)空元素，并且您想要該項(xiàng)或它后面的一個(gè)，則不會(huì)返回預(yù)期值?？紤]這個(gè)示例，其中第4個(gè)元素為NULL，我希望第5個(gè)元素，因此期望返回‘5’：

SQL> select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;R-6

驚喜！它返回第5個(gè)非空元素，而不是實(shí)際的第5個(gè)元素！返回的數(shù)據(jù)不正確，甚至可能抓不到它。試一試：

SQL> select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;R-5

因此，上面更正的REGEXP_SUBSTR表示查找0或更多逗號(hào)分隔字符的第5次出現(xiàn)，后面跟著逗號(hào)或行尾(允許下一個(gè)分隔符，無論是逗號(hào)還是行尾)，并在被發(fā)現(xiàn)時(shí)返回第一個(gè)子組(數(shù)據(jù)不包括逗號(hào)或行尾)。

搜索匹配模式'(.*?)(,|$)'解釋：

(             = Start a group.             = match any character*            
 = 0 or more matches of the preceding character?             = Match 0 or 1 occurrences of the preceding pattern)      
        = End the 1st group(             = Start a new group (also used for logical OR),             = comma|       
              = OR$             = End of the line)             = End the 2nd group

編輯：更多的信息添加和簡化了正則表達(dá)式。

有關(guān)更多信息和將其封裝在函數(shù)中以便于重用的建議，請參閱本文：REGEX從列表中選擇第n個(gè)值，允許為空這是我發(fā)現(xiàn)格式的那篇文章'[^,]+'有問題。不幸的是，這是您最常看到的regex格式，用于回答有關(guān)如何解析列表的問題。當(dāng)我想到所有不正確的數(shù)據(jù)時(shí)，我不寒而栗。'[^,]+'!

反對回復(fù) 2019-06-18

汪汪一只貓

TA貢獻(xiàn)1898條經(jīng)驗(yàn) 獲得超8個(gè)贊

如果您只有一行，并且有時(shí)間創(chuàng)建

創(chuàng)建您自己的內(nèi)置
cto_table 函數(shù)在任何分隔符上拆分字符串。
，然后你可以使用
PIVOT + LISTAGG
這樣做如下：

select * from (
  select rownum r , collection.*  
    from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection)PIVOT ( 
  LISTAGG(column_value) within group (order by 1) as val 
  for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

FYI：下面是如何創(chuàng)建cto_table職能：

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);CREATE OR REPLACEFUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_listAS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;END cto_table;/

反對回復(fù) 2019-06-18