Re: Dumb newbie question; how to identify numerics.

Jaywalk

wrap the call to to_number in a function

e.g

SQL> create table VorN
  2 (x varchar2(5))
  3 /

Table created.

SQL> insert into vorn values ('A');

1 row created.

SQL> insert into vorn values ('B');

1 row created.

SQL> insert into vorn values ('1');

1 row created.

SQL> insert into vorn values ('21');

1 row created.

SQL> CREATE OR REPLACE FUNCTION my_number( p_text IN VARCHAR2)   2 RETURN NUMBER
  3 IS
  4 BEGIN
  5 RETURN TO_NUMBER(p_text);
  6 EXCEPTION
  7 WHEN OTHERS THEN RETURN NULL;
  8 end;
  9 .
SQL> / Function created.

SQL> select x,my_to_number(x) from vorn;

X MY_TO_NUMBER(X)
----- ---------------
A
B
1
21

SQL> select x,my_number(x) from vorn where my_number(x) is not null;

X MY_NUMBER(X)
----- ------------

1                1
21              21

this will avoid the oracle error

regards

GA

"Jaywalk" <jaywalk_at_techie.com> wrote in message news:8f27f83a.0301301031.5804c48b_at_posting.google.com...
> This is embarassing. I can't for the life of me remember how to get a
> query to return results only when a certain VARCHAR2 field is not
> numeric. Some of the fields are numeric and some are not, but I can't
> do something like "to_number(fieldname)" to figure out which ones are
> numeric because it blows up on the first non-numeric.
>
> I've been through the manuals, FAQs and past posts and I'm still
> coming up blank. Is there a kind soul out there who can help me past
> this brain cramp I'm having?
>
> Any help would be appreciated.
>
> Jaywalk
Received on Thu Jan 30 2003 - 15:18:06 CST