pulling only numeric data from varchar data type field

[vsharmac]

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

[ixion]

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...

[tamilselvan]

Use Oracle provided function.

This example will help you.

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

[tamilselvan]

This one is better than the prev reply.

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

[WilliamR]

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.

[ixion]

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

[hobbes]

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)

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

[Sathy]

http://www.techonthenet.com/oracle/q.../isnumeric.php