4

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

String '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 and so on.

Sam
  • 513
  • 1
  • 11
  • 27

3 Answers3

11

based on https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement :

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.

realbart
  • 3,497
  • 1
  • 25
  • 37
3

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
Marcio Matias
  • 31
  • 3
1

If you have APEX 5.1 or higher installed in the your database, you can solve this quite elegantly, using apex_string.split :

select column_value
from table(apex_string.split('20.4,12.5,3.5,0.2,0.2', ','))
Hayden Hudson
  • 79
  • 5