Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in August 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close