Detect No Numeric String 2

[IanWaterman]

I am working witha database where designer insisted on using string fields to hold numeric data but did not add any validation to check data entered.

I now have to detect whether a field is non numeric on the fly.

In SQL server we have Isnumeric() and can test data, I can not find anything like this for Oracle.

Can any one suggest how I can test a string to check it is numeric?

Thank you

 

[Thargy]

This is one of those golden classics that defeated me for years. However, I offer the following:-

CREATE OR REPLACE FUNCTION IS_NUMERIC (string_to_check IN VARCHAR2)
RETURN NUMBER
AS

dummy_number NUMBER;

BEGIN

SELECT TO_NUMBER(string_to_check)
  INTO dummy_number
  FROM DUAL;
  
RETURN 1;

EXCEPTION WHEN OTHERS THEN RETURN 0;

END;

Usage is:-

 SELECT IS_NUMERIC('FRED') FROM DUAL;

Zero means it's not numeric, and 1 indicates that it is.
Switch the return data type to boolean if you just want to use this in PL/SQL, as its logically easier to deal with true and false (in my opinion anyway).
Hope this helps.

Regards

T

 

[taupirho]

I would replace Thargy's

SELECT TO_NUMBER(string_to_check)
INTO dummy_number
FROM DUAL;

with the simpler and probably more performant

dummy_number := string_to_check;


In order to understand recursion, you must first understand recursion.

 

[Thargy]

That's simpler - nice one tauphirho
Tested and working

CREATE OR REPLACE FUNCTION IS_NUMERIC (string_to_check IN VARCHAR2)
RETURN NUMBER
AS

dummy_number NUMBER;

BEGIN

dummy_number := TO_NUMBER(string_to_check);
RETURN 1;

EXCEPTION WHEN OTHERS THEN RETURN 0;

END;

Regards

T

 

[IanWaterman]

Thank you to you both.

That's great and will make my life a whole lot easier.

Ian

 

[SantaMufasa]

I am working with a database where designer insisted on using string fields to hold numeric data...

Then Ian, you are not working with a designer, you are working with a hack. Using a string column to store numeric-only data is like using bus tires on a bicycle. You could do it, but there is no good reason for it.

By the same token, in a quarter century of being an Oracle professional, no one has ever been able to show me a case for storing a DATE in a non-DATE column definition.

The bottom line: Don't ever accept a data design where a NUMBER or a DATE resides in a non-NUMBER or non-DATE data definition (respectively).


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[IanWaterman]

Mufasa

I have worked with the company that developed the software for 8 years and I have had similar conversations time and time again. They just do not care!!

Their objective is to make screen entry as simple as possible and do not ever consider data extraction for reporting.

I take the view that if they made it easy then every one would do it. Its kept me gainfully employed sorting out their data design hacks so I can't complain too much ;-)

Ian

 

[SantaMufasa]

Their objective is to make screen entry as simple as possible...

Ian, do they realize that data entry and its enforcement code is simplified when they leverage Oracle's built-in data-integrity capabilities? (I think not !) The first rule of "Data-integrity Enforcement" is "Don't allow bad data to survive any longer than the data-entry screen." By their not enforcing NUMBER on number-only data, they are allowing bad data to survive forever !

I know you are being facetious about "(their incompetence has) kept me gainfully employed...", but do you really want your job security to be based upon their lack of professionalism? Would you want to fly on an airplane designed by incompetent engineers, hoping that the builders catch bad designs during the manufacturing process?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Thargy]

Santa,

sadly Ian's post strikes a chord with me. All too often databases are "designed" around the whims of developers, who have no real understanding of referential integrity, data integrity, validation etc.
The goal is always to get it done quickly, and worry about cleaning up the mess later on.

The fact that the system is as trustworthy as a nine pound note seems to be of no significance.

Regards

T

 

[IanWaterman]

Thargy has hit the nail on the head. The "Developers" do not really understand databases. They are using them to populate a data entry screen and solve a data entry problem.

No one worries or cares about Management Info/Business Intelligence at that point. I have wasted too much breath complaining so just have to fix stuff as it comes along. My new Function is doing its job and every one is happy, another problem swept under the carpet. Though its looking a bit lumpy these days ;-)

Ian