REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

We can use oracle standard REGEXP_LIKE command to apply a regular expression on value.

Below is sample query to find if a value in a column is String or Number:

select first_name||' '||last_name "Emp Name"
,(case
  when regexp_like(first_name||' '||last_name, '^-?[[:digit:],.]*$')
  then 'Emp_name'||' is a NUMBER'
  else 'Emp_name'||' is a STRING'
  end) "O/P: STRING_OR_NUMBER"
,employee_number
,(case
  when regexp_like(employee_number , '^-?[[:digit:],.]*$')
  then 'Emp_number'||' is a NUMBER'
  else 'Emp_number'||' is a STRING'
  end) "O/P: STRING_OR_NUMBER"
from per_all_people_f;

as you can see  in the result below

We can also use this query as a function to use it in PL/SQL packages, something like below:

create or replace function xx_string_or_number(p_value in varchar2) 
return varchar2
as
    v_result varchar2(10) := 'NULL';
begin
    --
    select  (case
            when regexp_like(p_value, '^-?[[:digit:],.]*$')
            then 'The values in this column is : NUMBER'
            else 'The values in this column is : STRING'
            end) result
    into v_result
    from dual;
    --
return v_result;
end xx_string_or_number;

as you can see by putting this query in a function you can use it in any SELECT statement like :