SQL 将逗号分隔的值拆分为 Oracle 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31464275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split comma separated values to columns in Oracle
提问by user3407090
I have values being returned with 255 comma separated values. Is there an easy way to split those into columns without having 255 substr?
我的值返回了 255 个逗号分隔值。有没有一种简单的方法可以在没有 255 个 substr 的情况下将它们拆分成列？
ROW | VAL
-----------
1 | 1.25, 3.87, 2, ...
2 | 5, 4, 3.3, ....
to
到
ROW | VAL | VAL | VAL ...
---------------------
1 |1.25 |3.87 | 2 ...
2 | 5 | 4 | 3.3 ...
回答by Gary_W
Beware! The regexp_substr expression of the format '[^,]+'will not return the expected value if there is a null element in the list and you want that item or one after it. Consider this example where the 4th element is NULL and I want the 5th element and thus expect the '5' to be returned:
谨防！'[^,]+'如果列表中有一个空元素并且您想要该项目或它之后的一个，则该格式的 regexp_substr 表达式将不会返回预期值。考虑这个例子，其中第 4 个元素为 NULL，我想要第 5 个元素，因此期望返回 '5'：
SQL> select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;
R
-
6
Surprise! It returns the 5th NON-NULL element, not the actual 5th element! Incorrect data returned and you may not even catch it. Try this instead:
惊喜！它返回第 5 个非空元素，而不是实际的第 5 个元素！返回的数据不正确，您甚至可能无法捕捉到它。试试这个：
SQL> select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;
R
-
5
So, the above corrected REGEXP_SUBSTR says to look for the 5th occurrence of 0 or more comma-delimited characters followed by a comma or the end of the line (allows for the next separator, be it a comma or the end of the line) and when found return the 1st subgroup (the data NOT including the comma or end of the line).
因此，上面更正的 REGEXP_SUBSTR 表示要查找第 5 个出现的 0 个或多个逗号分隔字符后跟逗号或行尾（允许下一个分隔符，无论是逗号还是行尾）和找到时返回第一个子组（数据不包括逗号或行尾）。
The search match pattern '(.*?)(,|$)'explained:
搜索匹配模式'(.*?)(,|$)'说明：
( = 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
EDIT: More info added and simplified the regex.
编辑：添加了更多信息并简化了正则表达式。
See this post for more info and a suggestion to encapsulate this in a function for easy reuse: REGEX to select nth value from a list, allowing for nullsIt's the post where I discovered the format '[^,]+'has the problem. Unfortunately it's the regex format you will most commonly see as the answer for questions regarding how to parse a list. I shudder to think of all the incorrect data being returned by '[^,]+'!
有关更多信息和将其封装在一个函数中以便于重用的建议，请参阅此帖子：REGEX 从列表中选择第 n 个值，允许空值这是我发现格式'[^,]+'有问题的帖子。不幸的是，它是您最常看到的正则表达式格式，作为有关如何解析列表的问题的答案。想到'[^,]+'!返回的所有错误数据，我不寒而栗。
回答by Gordon Linoff
You can use regexp_substr():
您可以使用regexp_substr()：
select regexp_substr(val, '[^,]+', 1, 1) as val1,
regexp_substr(val, '[^,]+', 1, 2) as val2,
regexp_substr(val, '[^,]+', 1, 3) as val3,
. . .
I would suggest that you generate a column of 255 numbers in Excel (or another spreadsheet), and use the spreadsheet to generate the SQL code.
我建议您在 Excel（或其他电子表格）中生成一列 255 个数字，并使用该电子表格生成 SQL 代码。
回答by J. Chomel
If you only have one row, and time to create your
如果您只有一行，并且有时间创建您的
- create your own built-in
cto_tablefunction to split a string on any separator, then you can usePIVOT + LISTAGGto do it like follows:
- 创建您自己的内置
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:here is how to create the cto_tablefunction:
仅供参考：这是创建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;
/
回答by J. Chomel
hierarchical query could be used. pivoting can be done with case and group by.
可以使用分层查询。旋转可以通过 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;
