WITH t AS ( SELECT '1234' a FROM dual UNION ALL SELECT '1234A' FROM dual UNION ALL SELECT '1235' FROM dual UNION ALL SELECT '1236' FROM dual UNION ALL SELECT '1247B' FROM dual UNION ALL SELECT '12346C' FROM dual UNION ALL SELECT '12346D' FROM dual ) SELECT a -- 1. TRANSLATE -- , TRANSLATE(a, '0123456789'||a, '0123456789') b , TRANSLATE(a, 'a0123456789', 'a') c -- 2. REGEXP_SUBSTR , REGEXP_SUBSTR(a, '[0-9]+') b , REGEXP_SUBSTR(a, '[^0-9]+') c -- 3. REGEXP_REPLACE , REGEXP_REPLACE(a, '[^0-9]') b , REGEXP_REPLACE(a, '[0-9]') c FROM t ;