Oracle: Extract Numbers from String (Ex: Pin from Address)

SELECT REGEXP_SUBSTR(REPLACE(ADDRESS,' '),'[[:digit:]]{6}') FROM MYTABLE
WHERE REGEXP_SUBSTR(REPLACE(ADDRESS,' '),'[[:digit:]]{6}') IS NOT NULL;

Explanation:
It will extract the number (6 adjcent digits) from the address field


If you want to extact all numbers from string use any of below:

SELECT regexp_replace('12ZXC3ASD456FGH8TED63','\D') FROM dual;

SELECT regexp_replace('12ZXC3ASD456FGH8TED63','[^0-9]') FROM dual; 

SELECT regexp_replace('12ZXC3ASD456FGH8TED63','[^[:digit:]]') FROM dual;


Related Links:
- Oracle: Function ISNUMERIC Check Value is Number or Not
- Extracting text between html tags (removing html tags)
- Oracle: Extract Initials of Names