Re: select only numeric values from varchar2 column

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 20 Jul 1999 21:38:44 +0800
Message-ID: <37947BE4.70D3@yahoo.com>

Michael Ringbo wrote:
>
> Hi,
>
> As I read your posting, you only want columns where all the characters
> are numeric. Can't see any other way than the hard one:
>
> select col1
> from toto
> where (substr(col1,1,1) in ('0','1','2',.....'9'))
> and (substr(col1,2,1) in ('0','1','2',.....'9') or substr(col1,2,1) is
> null)
> and (substr(col1,3,1) in ('0','1','2',.....'9') or substr(col1,3,1) is
> null)
> and (substr(col1,4,1) in ('0','1','2',.....'9') or substr(col1,4,1) is
> null)
> .
> .
> .
> and (substr(col1,11,1) in ('0','1','2',.....'9') or substr(col1,11,1) is
> null)
> ;
>
> Hope you table is not too big!
>
> Regards,
>
> Michael Ringbo
>
> Bruno Decraene wrote:
>
> > I have a table toto with varchar column col1
> >
> > col1 contains alphanumeric values and numeric values and i want to
> > select only the numeric values
> >
> > How can i do this
> >
> > Thank you
> >
> > ansewer to me to rchennaa_at_atos-group.com
> >
> > Rach

or

create or replace function is_number(x varchar2) return number is   dummy number;
begin
  dummy := to_number(x);
  return 1;
exception when others then return 0;
end;

select *
from my_table
where is_number(col1);

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Tue Jul 20 1999 - 08:38:44 CDT