-- Oracle TRANSLATE Function to filter numeric data in a list of
-- numeric and non-numeric data

SELECT  q_tab.idx,  q_tab.trans_cd
FROM
(SELECT  100 idx, '100A00B7' trans_cd  FROM DUAL  UNION
 SELECT  101 idx, '10010027' trans_cd  FROM DUAL  UNION
 SELECT  102 idx, '100X0029' trans_cd  FROM DUAL  UNION
 SELECT  103 idx, '10010035' trans_cd  FROM DUAL
) q_tab
WHERE DECODE(TO_NUMBER(TRANSLATE(q_tab.trans_cd,'123456789','000000000')),0,0,-1) = 0; 

-- The result of the above query will fetch two index (idx) values 
-- 101 and 103

-- Other option is to use REGEXP_LIKE

SELECT  q_tab.idx,  q_tab.trans_cd
FROM
(SELECT  100 idx, '100A00B7' trans_cd  FROM DUAL  UNION
 SELECT  101 idx, '10010027' trans_cd  FROM DUAL  UNION
 SELECT  102 idx, '100X0029' trans_cd  FROM DUAL  UNION
 SELECT  103 idx, '10010035' trans_cd  FROM DUAL
) q_tab
WHERE REGEXP_LIKE(q_tab.trans_cd, '^[[:digit:]]+$')
;

SELECT  q_tab.idx,  q_tab.trans_cd
FROM
(SELECT  100 idx, '100A00B7' trans_cd  FROM DUAL  UNION
 SELECT  101 idx, '10010027' trans_cd  FROM DUAL  UNION
 SELECT  102 idx, '100X0029' trans_cd  FROM DUAL  UNION
 SELECT  103 idx, '10010035' trans_cd  FROM DUAL
) q_tab
WHERE REGEXP_LIKE(q_tab.trans_cd, '^\d+(\.\d+)?$')
;