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 !