This Content Component encountered an error

Oracle vs. open source challengers

Oracle and MySQL <<previous|next>> :MySQL 5.0 shoots for a new image

Oracle SQL to test for numerics

Oracle SQL to test for numerics

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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;
/

Thanks, Pete!

30 Aug 2007