Re: Dumb newbie question; how to identify numerics.

What you can do is write a small function which checks if the feild is a number then u can use this function in the where clause of ur query.

create or replace function is_number(v_str in varchar) return varchar2 is v_retval varchar2(1) ;
v_temp number;

begin
v_retval := 'N';

  begin
    v_temp := to_number(v_str );
    v_retval := 'Y';
  exception
    v_retval := 'N'
  end;

return v_retval;

end is_number;

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E397290.B5EC1706_at_exesolutions.com>...
> Jaywalk wrote:
>
> > 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
>
> What makes you think there is a way to do this in a simple SELECT
> statement?
>
> Now if you wanted to exclude
> 1234 and 12A23 but not ABCD it could be done.
>
> But to tell 123.456.789 from 123456789 isn't going to happen.
>
> Go for a stored procedure.
>
> Daniel Morgan
Received on Thu Jan 30 2003 - 19:43:16 CST