Get list of numbers from string (Oracle PL/SQL) - with Pipelined table function

remove symbol & convert comma separated values to rows

before start looking up script you can run this script to see the result in advance:

SELECT regexp_substr(regexp_replace(‘&STRING’, ‘[^A-Z0–9-,]’, ‘’), ‘[^,]+’, 1, LEVEL) a
FROM dual
CONNECT BY regexp_substr(regexp_replace(‘&STRING’, ‘[^A-Z0–9-,]’, ‘’), ‘[^,]+’, 1, LEVEL) IS NOT NULL;

after that enter text like that: 12as3,@aa@12d34,1a2345, a12226 (without quotes)

There are three basic steps to create table function

1. Create Type

CREATE OR REPLACE TYPE list_of_numbers as table of number

2. Create Function

3. Calling the Function

SELECT * FROM TABLE(get_list_numbers(‘210, 1202, 30$, 1920, 100, 15%’))

see full script on my gists page

Thanks.