我返回的值包含255个逗号分隔的值。 

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 ...


评论

为什么不使用Excel？并从那里编码？
..因为自动化过程位于数据库中。

#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代码。

评论

这不是正确的答案，因为该问题明确指出“没有255 substr”。另外，请注意Gary_W的答案，这是一个非常有效的担忧。

– Wouter
15年8月14日在11:39


@Wouter。 。 。答案是要在电子表格中生成值。

–戈登·利诺夫（Gordon Linoff）
15年8月14日在12:50
是;聪明。但仍然不是眼前问题的答案。

– Wouter
15年8月14日在14:09

#2 楼

谨防！如果列表中有一个null元素，并且您想要该项目或其后一个，则格式为'[^,]+'的regexp_substr表达式将不会返回预期值。考虑以下示例，其中第4个元素为NULL，而我想要第5个元素，因此希望返回“ 5”：

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

R
-
6


惊喜！它返回第5个NON-NULL元素，而不是实际的第5个元素！返回的数据不正确，您甚至可能无法捕获。请改用以下方法：

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

R
-
5


因此，上述已更正的REGEXP_SUBSTR表示要查找第5个出现的0个或多个逗号分隔字符，后跟逗号或末尾行（允许使用下一个分隔符，可以是逗号或行尾），找到后返回第一个子组（数据不包括逗号或行尾）。

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

(             = 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个值，允许为空
这是我发现'[^,]+'格式存在问题的帖子。不幸的是，它是正则表达式格式，您最常将其视为有关如何解析列表的问题的答案。我不禁要想到'[^,]+'返回的所有错误数据！

评论

感谢您的观察。这对我帮助很大。我在正则表达式的解释中发现了一个错误。这部分不正确：* = 0或多个前一个字符匹配？ =匹配前面模式的0或1次出现如果正则表达式不起作用，则不起作用。它应该以类似以下内容的形式显示：*？ =在0到无限制的时间之间进行匹配，并尽可能少地进行匹配，并根据需要进行扩展（延迟）

–bajermi2
1月30日9:35


当定界符为|时，它不起作用（管道）已插入，（逗号）

–吉坦德拉·帕特尔（Jitendra Patel）
8月24日12:53
@JitendraPatel我怀疑您在寻找'（。*？）（\ || $）'，您需要对文字管道进行转义，因为它是正则表达式语言中的特殊字符，表示OR。

– Gary_W
8月24日16:50


#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 REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  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;
/


#4 楼

可以使用分层查询。旋转可以使用大小写和分组依据。

with value_t as
( 
  select row_t,row_number() OVER (partition by row_t order by rownum )rn,
  regexp_substr(val, '[^,]+', 1, LEVEL) val from Table1
CONNECT BY LEVEL <= regexp_count(val, '[^,]+') 
AND prior row_t = row_t 
AND prior sys_guid() is not null
  ) select row_t, max( case when rn = 1 THEN val end ) val_1,
  max( case when rn = 2 THEN val end ) val_2,
  max( case when rn = 3 THEN val end ) val_3
  from value_t
  group by row_t;