The following are examples to split a string in Oracle using SQL query.

Split a String Using regexp_substr() Function in Oracle

The below query will split the string separated by comma using regexp_substr() function:

select regexp_substr('A,B,C,D',
                    '[^,]+', 1, level) from dual
 connect by regexp_substr('A,B,C,D',
                        '[^,]+',1,level) is not null;

Output

A
B
C
D

Split a String Using apex_split.string() Funtion in Oracle

If you have Oracle Apex installed on your database, you can use apex_split.string() function. Below is an example:

SELECT t.Column_ValueAS COL
  FROM TABLE(Apex_String.Split('A,B,C,D', ',')) t
  WHERE t.Column_Value IS NOT NULL;

Output

A
B
C
D