Re: select only numeric values from varchar2 column

There is another way:
If you do not have mixed feilds in the table (i.e. any entries that begin with a number and then contain characters) you can simplay ask for anything where the value is < 'A'. This runs into problems when you have entries that begin with numbers but contain text but is usefull in many applications.

Hope this helps.

Mike

Michael Ringbo <mhr_at_NOSPAMramboll.dk> wrote in message news:37943B5F.D7F6CBEA_at_NOSPAMramboll.dk...
> 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
>
Received on Tue Jul 20 1999 - 07:20:49 CDT