在Oracle中将逗号分隔的值拆分为列

Question

在Oracle中将逗号分隔的值拆分为列

23

我有255个逗号分隔的数值返回。是否有一种简单的方法可以将它们拆分成列，而不必使用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 ...

- user3407090

为什么不使用Excel？然后从那里编写代码呢？ - lookslikeanevo

1

因为自动化过程驻留在数据库中。 - alexherm

4个回答

21

您可以使用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代码。

- Gordon Linoff

3

这不是一个正确的答案，因为问题明确指出“没有255个子串”。还要注意Gary_W的回答，那是一个非常合理的关注点。 - Wouter

1

@Wouter . . . 这个答案的重点是在电子表格中生成值。 - Gordon Linoff

是的，聪明。但仍然不是对手头问题的答案。 - Wouter

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

提示：以下是创建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;
/

- J. Chomel

2

可以使用分层查询来实现。使用case和group by可以进行数据透视。

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;

- user9213118

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gary_W · Accepted Answer

注意！如果列表中存在空元素且你想获取该空元素之后的某个元素，那么格式为'[^,]+'的regexp_substr表达式将无法返回期望的值。考虑以下示例，其中第4个元素为NULL，我想获取第5个元素，因此期望返回'5':

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

R
-
6

惊喜！它返回的是第五个非空元素，而不是第五个实际元素！这可能会导致错误数据的返回，而你甚至可能都没有发现这个问题。请改用以下代码：

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

R
-
5

因此，上述已更正的 REGEXP_SUBSTR 表示查找第5个出现的0个或多个逗号分隔字符，后跟逗号或行尾（允许下一个分隔符，无论是逗号还是行尾），并在找到时返回第1个子组（数据不包括逗号或行尾）。

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

(             = 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 to select nth value from a list, allowing for nulls。这是我发现'[^,]+'格式存在问题的帖子。不幸的是，对于有关如何解析列表的问题，您最常见的答案就是这种正则表达式格式。想到所有错误的数据都被'[^,]+'返回，我感到震惊！