HOW TO FIND WHETHER A STRING IS NUMERIC OR NOT IN ORACLE

REGEXP_LIKE(my_col1, '[[:alnum:]]')
To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1 is the string value that you are testing

This function will return a null value if string1 is numeric.
It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))); would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))); would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))); would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));