How to check for valid numbers


-- select records from a table where a varchar field contains only valid numbers
-- Method 1
select emp_id,hours_worked
from emp
where trim(translate(hours_worked,'+-.0123456789',' ')) is null

translate(expr_string, from_string, to_string) converts searches through expr_string and replaces every character in from_string to the corresponding character in to_string. If no corresponding character is found (i.e. the nth character in from_string has no corresponding character in to_string), then the character is removed.
Examples

• translate('12345', '14', '69') => '69345'
• translate('12345', '41', '96') => '69345'
• translate('12345', '12345', ' ') => ' '

Note that to_string must have at least one character (a space in the last example) otherwise all characters are removed because to_string is effectively NULL.
Note also that this will not work for values with the pattern 9999-9999
This function can be used to test if a value is numeric by trimming as shown in the SQL snippet above.
-- Method 2
select emp_id,hours_worked
from emp
where regexp_like(hours_worked,'^\d+$')