Simple is good – an ISNUMBER Function for PL/SQL

A while ago, I was presented with a requirement to read in a variable which could either be a character string or an integer, the subsequent data lookup being dependent on whether the variable was numeric or not.
My initial reaction was to think of writing a generic ISNUMBER function to do this. However, after brief consideration, I realised that, if it was to be truly generic, it would have to handle any numeric values, not just whole numbers. Furthermore, the value passed in maybe negative.
Initially, I came up with this :-

CREATE OR REPLACE FUNCTION isnumber( pa_string VARCHAR2)
   RETURN BOOLEAN IS
   l_ascii PLS_INTEGER;
   l_decimal BOOLEAN := FALSE;
   l_pos PLS_INTEGER := 1;
BEGIN
   --
   -- First character may be a "-"
   --
   IF SUBSTR( pa_string,1,1) = '-' THEN
      --
      -- start checking from the second character
      -- provided the string is more than one character
      IF LENGTH( pa_string) > 1 THEN
         l_pos := 2;
      ELSE
         RETURN FALSE;
      END IF;
   END IF;
   --
   -- Now loop through the input string looking for non-numeric characters
   -- NOTE - we need to be aware that there may be a decimal in here
   -- somewhere
   --
   FOR i IN l_pos..LENGTH(pa_string) LOOP
      l_ascii := ASCII(SUBSTR( pa_string, i, 1));
      IF l_ascii = 46 THEN
         -- decimal point. Is this the  first one we've hit ?
         IF l_decimal THEN
            -- already encountered one
            -- so this is not a number
            RETURN FALSE;
         ELSE
            -- need to remember that we've hit a decimal point
            -- as a number can contain only 1
            l_decimal := TRUE;
         END IF;
      ELSIF l_ascii NOT BETWEEN 48 AND 57 THEN
         --
         -- not a character between 0 and 9 therefore this isn't a number
         --
         RETURN FALSE;
      END IF;
   END LOOP;
   --
   -- If we get here then we know it's a number
   --
   RETURN TRUE;
END;

I was feeling quite pleased with myself, until a colleague of mine showed me his version :-

CREATE OR REPLACE FUNCTION isnumber( pa_string VARCHAR2) RETURN BOOLEAN IS
   l_dummy NUMBER;
BEGIN
   l_dummy := TO_NUMBER( pa_string);
   RETURN TRUE;
EXCEPTION
   WHEN INVALID_NUMBER THEN
   RETURN FALSE;
END;

Note that there’s no WHEN-OTHERS exception. If something other than a VARCHAR2, NUMBER or DATE is passed in then it’s likely something’s gone horribly wrong so we want the function to complain…loudly.
No mucking about with ASCII; no walking through the entire input string; and basically ONE active line of code :-
   l_dummy := TO_NUMBER( pa_string);
I know which one I’d rather support.
In the words of Leonardo Da Vinci – “Simplicity is the ultimate sophistication.”
OK, so I was going to quote Ron Greenwood ( “Simplicity is genius”), but Da Vinci looks far more impressive !

About these ads