Query to test if a value is a number or string

Using Oracle's REGEXP_LIKE function, you can easily find out whether a value is a number or a string.

-------------------------------------------------------------------------------

-- Query to test if a value is a number of string

-------------------------------------------------------------------------------

DECLARE

   test_string  VARCHAR2(80);

   v_string     VARCHAR2(80);

  

BEGIN

  

   test_string := '123';

   -- test_string := 'A123';

  

   SELECT CASE

             WHEN REGEXP_LIKE(test_string, '^-?[[:digit:],.]*$') THEN

                test_string || ' is a NUMBER'

             ELSE

                test_string || ' is a STRING'

          END

     INTO v_string

     FROM dual;

  

   DBMS_OUTPUT.PUT_LINE(v_string);

  

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END;