Re: fastest "is number" oracle 9i

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 14 May 2007 05:58:19 -0700
Message-ID: <1179147499.840062.53000@l77g2000hsb.googlegroups.com>

On May 12, 8:05 pm, valigula <valig..._at_gmail.com> wrote:
> HI
> What is the fastest way on returning all the valids numerics from a
> column.
> MAT_CODIGO
>
> these are valids code
>
> IA9903000395
> VD9903300268
> VD9902700081
> VD9904100358
> VC9804105617
> VD9903300267
> VC9903300088
> VC9904101559
> VC9804105613
> VD9902700080
> VD9904100212
> 2000001054007
> VD9904100363
>
> and there are some as:
> VD990410.363
>
> Thanks in advance ...

Hi, all your sample data is of a regular format, contain digits, and the only one that is a number is 2000001054007. My understanding of your requirement is VC9904101559 should return 9904101559, 2000001054007 should return 2000001054007, and VD990410.363 should return 990410.363. If this is correct, then given the regular format of the column data, the code snippet I've written below is a potential solution for you.

Your requirements are also:

Oracle 9i. Although I have demonstrated the code on 10.2, "translate" is supported in 9i. See page 6-188 of #A96540-02 "Oracle 9i SQL Reference, release 2 (9.2)".
"the fast way on returning all the valid numerics from a column". See what others contribute to your post, code up, and compare the results.

Good luck.
~
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL> SELECT

colVal,REPLACE(TRANSLATE(colVal,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' ','') colNumber
2 FROM tblTest;

COLVAL COLNUMBER

-------------------- --------------------
VD9903300268         9903300268
2000001054007        2000001054007
VD990410.363         990410.363
VC9904101559         9904101559

Elapsed: 00:00:00.01
SQL>
SQL> SELECT *
2 FROM V$VERSION; BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production NLSRTL Version 10.1.0.4.0 - Production Received on Mon May 14 2007 - 07:58:19 CDT