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;