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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With