Re: select only numeric values from varchar2 column

From: Michael Ringbo <mhr_at_NOSPAMramboll.dk>
Date: Tue, 20 Jul 1999 11:03:27 +0200
Message-ID: <37943B5F.D7F6CBEA@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 - 04:03:27 CDT