-- 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+)?$') ;
52819