如何在Oracle中拆分逗号分隔的字符串 [英] How to Split a comma separated string in Oracle

问题描述

如何在Oracle中使用SUBSTR和INSTR分隔逗号分隔的字符串.

How to Split a comma separated string in Oracle using SUBSTR and INSTR.

字符串'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

12.5

3.5等.

推荐答案

基于 https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-选择子句中的声明:

首先，我们将形成一个查询，该查询将以逗号分隔的字符串拆分，并以行的形式给出各个字符串.

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.

这篇关于如何在Oracle中拆分逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！