Re: Boolean Function in SQL

From: <huiming_at_my-deja.com>
Date: Thu, 19 Oct 2000 02:41:38 GMT
Message-ID: <8sln11$1gm$1@nnrp1.deja.com>

Dear Connor,

I found a better way to do it, just a small modification on yours

It should be

select

     decode(
       replace(translate(col_name, '1234567890', '9999999999'), '9'),
               NULL, 'NUMBER', 'NOT A NUMBER')
       from table;

It will not depend on any specific characters like what you used '@'. As you may find easily, replace function can take 2 parameters. In this case, it will delete the character from the input string and this is what we want.

Cheers

In article <39DDB3C9.7D35_at_yahoo.com>,
connor_mcdonald_at_yahoo.com wrote:
> huiming_at_my-deja.com wrote:
> >
> > >
> > > select
> > > decode(
> > > replace(
> > > translate(col,'0123456789','@@@@@@@@@@'),
> > > '@',null),
> > > null,'IS A NUMBER',
> > > 'IS NOT A NUMBER)
> > > from table
> > >
> >
> > Very good idea. However this will not return the correct result when
> > column contains something like '1234_at_7@'.
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> True - but I'm sure a workable character can be found that the poster
> can be reasonably confident will not be in his source data...

> Cheers
> C
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk

> We are born naked, wet and hungry...then things get worse
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 18 2000 - 21:41:38 CDT