如何在 Oracle 中拆分逗号分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44260177/
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
How to Split a comma separated string in Oracle
提问by Sam
How to Split a comma separated string in Oracle using SUBSTRand INSTR.
如何在 Oracle 中使用SUBSTR和拆分逗号分隔的字符串INSTR。
String '20.4,12.5,3.5,0.2,0.2'.
字符串 '20.4,12.5,3.5,0.2,0.2'。
I tried using the below code, but I'm unable get the value after the 2nd comma.
我尝试使用以下代码，但无法获取第二个逗号后的值。
SELECT substr('20.4,12.5,3.5,0.2,0.2',0,instr('20.4,12.5,3.5,0.2,0.2',',')-1)
value FROM dual -- 1. 20.4
for second value i'm getting the entire string after 2nd comma.
对于第二个值，我在第二个逗号之后获取整个字符串。
SELECT substr('20.4,12.5,3.5,0.2,0.2',instr('20.4,12.5,3.5,0.2,0.2',',')+1,instr('20.4,
12.5,3.5,0.2,0.2',',',2,2)-1) st FROM dual -- result : 12.5,3.5,
I want the value after each comma, like
我想要每个逗号后的值，比如
20.4
20.4
12.5
12.5
3.5 and so on.
3.5等。
回答by realbart
First, we will form a query, that splits this comma separated string and gives the individual strings as rows.
首先，我们将形成一个查询，该查询拆分此逗号分隔的字符串并将单个字符串作为行给出。
SQL> select regexp_substr('20.4,12.5,3.5,0.2,0.2','[^,]+', 1, level) from dual
connect by regexp_substr('20.4,12.5,3.5,0.2,0.2', '[^,]+', 1, level) is not null;
REGEXP_SUBSTR('20.4,1
---------------------
20.4
12.5
3.5
0.2
0.2
The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.
上面的查询遍历逗号分隔的字符串，搜索逗号 (,)，然后通过将逗号视为分隔符来拆分字符串。每当遇到分隔符时，它都会将字符串作为一行返回。
回答by Marcio Matias
You can also check for a specific occurrence, sample, ever second occurrence between comma.
您还可以检查逗号之间的特定出现、示例、第二次出现。
SQL> select regexp_substr('20.4,12.5,3.5,0.2,0.2','[^,]+[^,]', 1,2 ) as 2nd_occur
from dual;
", 1,2 )" -- You can replace this query part to choose with occurrence you want, like
", 1,3 )" -- for third occurrence.`
Output:
2nd_occur
---------
12.5
