Re: fastest "is number" oracle 9i

On 14 mayo, 14:58, Michael O'Shea <michael.os..._at_tessella.com> wrote:
> 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:
>
> 1. 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)".
> 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.OS..._at_tessella.com
> __/__/__/ Tessella Support Services plc
> __/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
> __/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301www.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- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Thanks guys
Actually the "." is not a valid character as "-" and many more that are in the columns.
The definition is, valid values are:
First two characters can be alphabetic or numeric. The rest must be numeric.

When loaded the table the insert some codes that contain invalid charactersas "." or "-" etc .... i am trying to find a quick way of filtering all those wrong codes for deleting then. And making a procedure to implement when the tables are load.

Thanks

A. Received on Mon May 14 2007 - 13:35:30 CDT