We can use oracle standard REGEXP_LIKE command to apply a regular expression on value.
Below is sample query to find if a value is String or Number:
SELECT
CASE
WHEN REGEXP_LIKE('111', '^-?[[:digit:],.]*$')
THEN '111'
|| ' is a NUMBER'
ELSE '111'
|| ' is a STRING'
END "O/P: STRING_OR_NUMBER"
FROM dual;
We can also convert this query as a function to use it in PL/SQL packages, something like below:
CREATE OR REPLACE FUNCTION xx_string_or_number_f(
p_value IN VARCHAR2)
RETURN VARCHAR2
AS
p_output VARCHAR2(10) := 'NULL';
BEGIN
SELECT
CASE
WHEN REGEXP_LIKE(p_value, '^-?[[:digit:],.]*$')
THEN 'NUMBER'
ELSE 'STRING'
END
INTO p_output
FROM dual;
RETURN p_output;
END xx_string_or_number_f;
/