Oracle SQL Regular Expression to find if a Value is String or Number

Shailender Thallam

9 years ago

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

Testing

For Further Reading