> > On May 12, 8:05 pm, valigula <valig@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@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.