This article show how to translate string separated by a character

'STR1,STR2,STR3,STR4'

into rows

STR_REC               
-------------------
STR1
STR2
STR3
STR4

CONNECT BY

This method uses regular expressions and CONNECT BY. Nice but can be hard for read and use

SELECT regexp_substr('STR1,STR2,STR3,STR4','[^,]+', 1, level) str_rec
  FROM dual
CONNECT BY LEVEL <= (1+regexp_count('STR1,STR2,STR3,STR4', ',')); 
STR_REC               
-------------------
STR1               
STR2               
STR3               
STR4

COLLECTION

Almost the same as above but I have wrapped it in function to simplify sql statements

CREATE OR REPLACE TYPE t_agg_rec AS OBJECT
(
  str_rec CLOB
)
/

CREATE OR REPLACE TYPE t_agg_tbl FORCE AS TABLE OF t_agg_rec;
/

CREATE OR REPLACE FUNCTION FN_SPLIT_CONVERTED
(
  in_str        IN CLOB,
  in_del        IN VARCHAR2
)
RETURN t_agg_tbl PIPELINED 
AS 
BEGIN
  FOR i IN (
    SELECT regexp_substr(in_str,'[^'||in_del||']+', 1, level) str_rec
      FROM dual
    CONNECT BY LEVEL <= (1+regexp_count(in_str,in_del)))
  LOOP
    PIPE ROW(t_agg_rec(i.str_rec));
  END LOOP;
  
  RETURN;
END FN_SPLIT_CONVERTED;

SELECT * 
  FROM 
TABLE(fn_split_converted('STR1,STR2,STR3,STR4', ','));

STR_REC   
----------
STR1
STR2
STR3
STR4

So it can be easy used in subqueries with IN clause

SELECT * 
  FROM <TABLE_NAME>
 WHERE <COLUMN_ID> IN 
    (SELECT * FROM 
     TABLE(fn_split_converted('STR1,STR2,STR3,STR4', ','))

XML

Another option using xmltable method

SELECT (COLUMN_VALUE).getClobVal() str_rec
  FROM xmltable
       (
        ('"'||REPLACE('STR1,STR2,STR3,STR4', ',', '","')||'"')
       );

STR_REC               
-------------------
STR1
STR2
STR3
STR4

I believe that world as you know it is easier now ;o)

Have a fun 🙂

Tomasz