Re: SQL Function

Sybrand Bakker wrote:

> On Mon, 29 Apr 2002 16:13:48 GMT, Daniel Morgan
> <dmorgan_at_exesolutions.com> wrote:
>
> >Prasad Yarlagadda wrote:
> >
> >> Is there any standard SQL function available to check whether an array of
> >> characters is a String or a Number? I am looking for something like
> >> IS_NUMBER or IS_CHARACTER. I don't want to write a function for it if oracle
> >> has already provided.
> >>
> >> TIA,
> >> Prasad
> >
> >No. This must be done in a PL/SQL block such as the following:
> >
> >BEGIN
> > test := TO_NUMBER(somevalue);
> > <do something>
> >EXCEPTION
> > WHEN OTHERS THEN
> > <do something else>
> >END;
> >
> >If you don't do it this way you need to be very careful about what you decide is
> >a number. For example ... is 8.1.7.3 a number? It would be with a lot of code
> >I've seen written.
> >
> >Daniel Morgan
>
> An alternative solution would be replace all numeric characters by 'X'
> and check whether the string contains all X-es now
> REPLACE(<affected_column>,'0123456789','XXXXXXXXXX') =
> RPAD('',length(<affected_column>,'X')
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Except, as I pointed out, if your number contains a decimal point. For example:

REPLACE(8.1.7.3, '.0123456789','XXXXXXXXXXX') yields the same result as

REPLACE(8173000, '.0123456789','XXXXXXXXXXX') One of them is not a number.

Daniel Morgan Received on Mon Apr 29 2002 - 12:36:48 CDT