将逗号分隔为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;/