Oracle Training Oracle Support Development Oracle Apps

    Extract all comma delimited strings in Oracle SQL

                          Oracle Database Tips by Donald BurlesonMay 13, 2015

Question:  How do I split a comma-delimited string in Oracle? I have a string that is delimited by commas and I want to extract each string onto a separate line based on the comma delimiters. What is the best way of extracting comma delimited string in Oracle SQL?

Answer:  You can use the Oracle regex functions to extract a series of comma delimited strings. The following example demonstrates this principle of extracting a string that is enclosed with commas:


SQL> var input varchar2(150)
SQL> exec :input := 'ABC/DEF,GHI/JKL,MNO/PQR'

PL/SQL procedure successfully completed.

The first part is to split the string using the regexp_substr,
then split up the string using the comma as a delimiter.

select regexp_substr (:input, '[^,]+',1, rownum) str
from dual
connect by level <= regexp_count (:input, '[^,]+') ;



Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publishers.