pulling only numeric data from varchar data type field
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: pulling only numeric data from varchar data type field

  1. #1
    Join Date
    Jun 2006
    Posts
    1

    pulling only numeric data from varchar data type field

    Hi gurus,

    I am trying to pull data in a text file using TEXT_IO. from Oracle 9i database. Iam using forms 6i. There are 6 columns in that table and all are of data type varchar2.

    I want only that rows that have numeric values in column X and column Y. well its like (select everything from table where column X and column Y have numeric values)

    Iam able to use the TEXT_IO perfectly but iam not able to restrict the data.

    FUNCTION catg_download(
    clas_alpha VARCHAR2,
    path VARCHAR2)
    RETURN BOOLEAN IS
    BEGIN
    DECLARE
    LvfileId TEXT_IO.FILE_TYPE;

    CURSOR C1 IS
    SELECT X,Y,Z,A,B,C from TEMP;

    BEGIN
    -- Open the file for output
    LvFileId := TEXT_IO.Fopen(path,'w');

    TEXT_IO.PUTF(LvFileId,'%s,','X');
    TEXT_IO.PUTF(LvFileId,'%s,','Y');
    TEXT_IO.PUTF(LvFileId,'%s,','Z');
    TEXT_IO.PUTF(LvFileId,'%s,','A');
    TEXT_IO.PUTF(LvFileId,'%s,','B');
    TEXT_IO.PUTF(LvFileId,'%s,','C');

    FOR Rec IN C1 LOOP

    TEXT_IO.PUTF(LvFileId,'%s,',REC.X);
    TEXT_IO.PUTF(LvFileId,'%s,',REC.Y);
    TEXT_IO.PUTF(LvFileId,'%s,',REC.Z);
    TEXT_IO.PUTF(LvFileId,'%s,',REC.A);
    TEXT_IO.PUTF(LvFileId,'%s,',REC.B);
    TEXT_IO.PUTF(LvFileId,'%s\n',REC.C);


    END LOOP;
    -- Close ASCII file
    TEXT_IO.Fclose(LvfileId);
    END;
    RETURN NULL; END;

    Any help on this will be very much appreciated

    vsharma

  2. #2
    Join Date
    Jun 2006
    Posts
    259
    Attempt to convert fields x/y using to_number(rec.x)...

    Handle the exception "01722: invalid number" for data that can't be converted.. skipping the record. You'll need to add PL/SQL exception clauses around these...

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use Oracle provided function.

    This example will help you.

    Code:
    SQL> select * from t1 ;
    
    ID
    ----------
    10
    ABCXYZ
    30
    xyzabchfh
    
    SQL> get x1
      1  select * from
      2  ( select translate(lower(id),
                        'abcdefghijklmnopqrstuvwxyz',-1) tr from t1)
      3* where tr != -1
    SQL> /
    
    TR
    ----------
    10
    30
    Tamil

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This one is better than the prev reply.

    Code:
    SQL> select * from t1 ;
    
    ID
    ----------
    
    10
    abc123
    12ndh
    
    SQL> select *
      2   from ( select id, translate(lower(id), 'abcdefghijklmnopqrstuvwxyz',
      3                 '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$') tr  from t1)
      4  where instr(tr,'$') = 0 ;
    
    ID         TR
    ---------- ----------
              
    10         10
    Tamil

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    While using built-in SQL functions directly will be more efficient, the pattern matching approach always risks rejecting valid numbers like '1e5' and allowing invalid ones like '1.2.3' (or perhaps '1.2' should be invalid if your decimal character is not '.') and so a custom IS_NUMBER function as I think ixion is describing should be more reliable.

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Here is a quick function and test case:

    create or replace
    function is_number (inStr varchar2 )
    return integer
    IS
    n number;
    BEGIN
    n := to_number(inStr);
    RETURN 1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
    END;
    /



    create table test1 ( str varchar2(40) )
    tablespace users_std_Tbl;

    insert into test1 values( 'AAA');
    insert into test1 values ( '10');
    insert into test1 values ( '1.1');
    insert into test1 values ('1.2.2');
    insert into test1 values ('1/2');
    insert into test1 values ('.5');
    insert into test1 values ('1e5') ;
    commit;


    SQL> select str from test1 where is_number(str) = 1;

    STR
    ----------------------------------------
    10
    1.1
    .5
    1e5

    SQL> select * from test1;

    STR
    ----------------------------------------
    AAA
    10
    1.1
    1.2.2
    1/2
    .5
    1e5

    Let us know How this works out.
    Ken

  7. #7
    Join Date
    May 2006
    Posts
    12
    You could combine the efficiency of the pattern-matching approach in SQL with the completeness provided by a custom PL/SQL function.

    Use replace/translate to filter out those strings that cannot be numbers (e.g. "ABC") , and then use the is_number() check only for those that pass the first test and might be numbers.

    (Using ixion's is_number function)
    Code:
    SQL> select * from test1;
    
    STR
    ----------------------------------------
    AAA
    10
    1.1
    1.2.2
    1/2
    .5
    1e5
    -10
    
    8 rows selected.
    
    SQL> select *
      2  from test1
      3  where decode( 
      4  replace(translate(str,'1234567890.-e','0000000000000'),'0',''),
      5                                           NULL, is_number(str),
      6                                                0 ) = 1;
    
    STR
    ----------------------------------------
    10
    1.1
    .5
    1e5
    -10

  8. #8
    Join Date
    Mar 2004
    Location
    India
    Posts
    72

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width