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; /
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.