Two solutions for a SQL version of the apex_string.split (or apex_util.string_to_table in older Apex versions) PL/SQL function, for both strings and numbers.
I needed a pure SQL version to create insert statements with; of course I could have used a PL/SQL procedure but this was a lot simpler. And it performs very well.
Change the : to whatever you need, and remove the distinct if needed.
Note that apex_util.string_to_table was only introduced with Apex 5.1 (and apex_string.split in a later version), so I have also include the "OLD" code sections for older versions which use xmltable (and work just as well, but may be slower).