On 14 mei, 20:35, valigula <valig..._at_gmail.com> wrote:
> 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.
Split it into two columns, one being 2 positions alpha (varchar2(2)),
and the second being numeric. (Your model needs rework)
Any non-numeric data will be rejected immediately - sql*loader can
do that for you, and eevn produce a table with all rejections, so you
do not have to load then again. (Still no indication why
you insist on doing things the hard way)
Received on Tue May 15 2007 - 04:33:04 CDT
