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; / |