Q

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.

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!

This was first published in August 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close