SQL query for derived tables in a custom domain (advanced reporting)

[Legacy User]

Hi guys, we've built our own custom domain in advanced reporting and within this have incorporated our own SQL to build derived tables. We're struggling with how to extract data from an 'Ideas' table, and from a custom column in particular that stores multiple values. This column stores multiple values as a single string separated by semicolon (;), and we cant quite find the best functions to extract the individual values in PL SQL (Oracle). SPLIT couldn't be recognised and SUBSTR worked but could become a fairly complex query. 

Here's an example to illustrate:
We created a column called Products where the user can multi-select and find values as follows in the database table:
prod_a;prod_b;prod_c

prod_a;prod_c

prod_a

prod_b;prod_c

etc

 

Any pointers would greatly help. 

Thanks!

[Kritika Rana]

regexp_substr function can be used for this. 

Following link can also be referred :

How to split comma separated string and pass to IN clause of select statement (ADF Illusions)