Re: Reading VARCHAR fileds as Integers ...

From: Noel <tbal_at_go2.pl>
Date: Thu, 27 Nov 2003 10:33:22 -0000
Message-ID: <bq4g8t$5d6$1@inews.gazeta.pl>

Użytkownik "Michael J. Moore" <NOhicamelSPAM_at_comcast.net> napisał w wiadomości news:fPdxb.319205$Fm2.332915_at_attbi_s04...
> Gee, lets see...
> SQL> create table test (el1 varchar2(20));
> Table created.
> SQL> create view vtest as select to_number(el1) num from test;
> View created.
> SQL> insert into test values ('123');
> 1 row created.
> SQL> commit;
> Commit complete.
> SQL> select * from vtest;
>
> NUM
> ----------
> 123
>
> SQL>
>
>
> Yep, seems to work.
>

Sometimes doesn't...

SQL> insert into test values('123 '); 1 row crated.
SQL> commit;
Commit complete.
SQL> select * from vtest;
ERROR:
ORA-01722:
no rows returned.

SQL> create or replace function is_number(f_in VARCHAR2) RETURN NUMBER IS   2 f_out NUMBER;
  3 BEGIN
  4 SELECT TO_NUMBER(f_in) INTO f_out FROM DUAL;   5 RETURN(f_out);
  6 EXCEPTION WHEN OTHERS THEN RETURN(NULL);   7 END;
  8 /

Function created.

SQL>create or replace view vtest as select is_number(el1) num from test;

View created.

SQL> select * from test;

EL1

---

123
123

A bit better... But i would rather create a new column in a table with number type,
upate it with values from old varchar2 values, drop old one, rename new one to old one.

--
Noel

Received on Thu Nov 27 2003 - 04:33:22 CST