Oracle vs. open source challengersOracle and MySQL <<previous|next>> :MySQL 5.0 shoots for a new image
Oracle SQL to test for numerics
With reference to your answer to the question How can SQL test for pure numerics? I was surprised that since the column is headed "Ask the Oracle Expert" you did not provide an Oracle solution to the question.
Great question. Other regular Oracle readers of this column must surely also have noticed the scarcity of Oracle examples. A wee explanation is in order.
This column has always, since its inception over six years ago, been about just SQL, with as few proprietary or non-standard wrinkles as possible. (See the list of expert categories.) Naturally, as most of you are aware, standard SQL doesn't always work on your database of choice, nor does it have what you could call a robust set of functions (date calculation and string manipulation come easily to mind). So the examples in the solutions often had to use some particular flavour of SQL.
A few years later, TechTarget decided to re-align its "Search" portals with technology platforms. Gone was the generic database web site, and so this column had to be assigned elsewhere. SearchOracle.com is where it ended up. Unfortunately, PL/SQL is the weakest of my SQL dialects, which is why there are so few Oracle examples.
Reader Pete from Sheffield, who asked this follow-up question, was kind enough to provide the following Oracle code:
CREATE OR REPLACE FUNCTION is_numeric (p_in VARCHAR2) RETURN VARCHAR2 IS /* Returns 'T' if the value is NUMERIC 'F' otherwise */ l_temp NUMBER; l_return VARCHAR2(1) := 'F'; BEGIN SELECT p_in INTO l_temp FROM DUAL; l_return := 'T'; RETURN l_return; EXCEPTION WHEN OTHERS THEN RETURN l_return; END; /
30 Aug 2007