Re: Simple problem...

Hello Rene,

See comments in line.

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b3o1qd$1o5to9$1_at_ID-82536.news.dfncis.de>...
> > You can do it without exceptions if you use translate to remove all the
> > numeric characters and check for null. See this example below. Note the
> > 'x' is just a dummy translation to itself so that transalate won't always
> > return null.
> >
> > SQL> select * from t;
> >
> > STRING
> > --------------------
> > 12345
> > a12345
> > 12b345
> > 12b345xx
> > 987
> > 987.01
> > 1,987.01
> > 987*
> > 999
> > 953
> >
> > 10 rows selected.
> >
> > SQL> select to_number(string)
> > 2 from t where
> > 3 translate(string,'x0123456789','x') is null;
> >
> > TO_NUMBER(STRING)
> > -----------------
> > 12345
> > 987
> > 999
> > 953
>
> Unfortunately, this solution cannot handle strings in the scientific notation
> (how it is called, I believe) which is considered a valid number in oracle:
>
> SQL> select '5e8' * '3e-2' from dual;
>
> '5E8'*'3E-2'
> ------------
> 15000000
>

I didn't mean to suggest the brief examples I gave would handle all cases, just to give an alternative approach that can exclude or minimize the need to handle exceptions. Although I think here you could face the opposite problem with to_number if, for example '1001E1' is not supposed to be a number.

Other strings that could be considered numeric in certain cases would create problems with the to_number solution that also require work arounds, like '10,000', '$100.00',

>
> Not to mention that negative numbers cannot be converted, either.
>

You could handle negative numbers easily by adding '-' to the translate string, then again you would have the problem for two dashes you noted with two dots. But in some cases you might want to ignore dashes when having to process something like telephone numbers.

>
> > SQL> select to_number(string)
> > 2 from t where
> > 3 translate(string,'x.0123456789','x') is null;
> >
> > TO_NUMBER(STRING)
> > -----------------
> > 12345
> > 987
> > 987.01
> > 999
> > 953
>
> This is a problem if a string has more than one dots in it.
>

Ah that is a bit of a problem. Still if you were expecting two dots or even dashes, you could combine it with the safe exception handling to_number function, but then you would only have call that for each row that has already been pre-filtered using translate. Or even use instr and decode to only call it when problematic characters are found if performance is a real problem. It could certainly be faster than catching all exceptions.

Its all about alternatives and knowing what data you have to handle. Both approaches have advantages and disadvantages in different situations, but sometimes the purely to_number based solution can be just too slow.

Martin

>
> Rene Nyffenegger
Received on Fri Feb 28 2003 - 20:05:56 CST