将逗号分隔为Oracle中的列

Question

将逗号分隔为Oracle中的列返回值时使用255个逗号分隔的值。是否有一种不需要255个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 ...

Answer 1

你可以用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个数字的列，并使用电子表格生成SQL代码。

Answer 2

小心！格式的regexp_substr表达式'[^,]+'如果列表中有一个空元素，并且您想要该项或它后面的一个，则不会返回预期值。考虑这个示例，其中第4个元素为NULL，我希望第5个元素，因此期望返回‘5’：

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

惊喜！它返回第5个非空元素，而不是实际的第5个元素！返回的数据不正确，甚至可能抓不到它。试一试：

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

因此，上面更正的REGEXP_SUBSTR表示查找0或更多逗号分隔字符的第5次出现，后面跟着逗号或行尾(允许下一个分隔符，无论是逗号还是行尾)，并在被发现时返回第一个子组(数据不包括逗号或行尾)。

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

(             = 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

编辑：更多的信息添加和简化了正则表达式。

有关更多信息和将其封装在函数中以便于重用的建议，请参阅本文：REGEX从列表中选择第n个值，允许为空这是我发现格式的那篇文章'[^,]+'有问题。不幸的是，这是您最常看到的regex格式，用于回答有关如何解析列表的问题。当我想到所有不正确的数据时，我不寒而栗。'[^,]+'!

Answer 3

如果您只有一行，并且有时间创建

• 创建您自己的内置

  cto_table 函数在任何分隔符上拆分字符串。

  ，然后你可以使用

  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：下面是如何创建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;/