_Andy_ wrote:
>
> On Mon, 24 Sep 2001 17:14:49 +0200, "Peter Laursen"
> <pl_at_mail1.remove.this.stofanet.dk> wrote:
>
> >> SELECT text text_value,TO_NUMBER(text) number_value FROM
> >>my_table;
> >>
> >> If my_table.text is numeric, there's no problem. However,
> >if
> >>it's not numeric, I get an ORA-01722.
> >>
> >You could write your own function. It would slow down your
> >select somewhat.
> >
> >CREATE OR REPLACE function to_number_null (a varchar2)
> >return number
> >as
> >begin
> > return to_number(a);
> >exception
> > when others then
> > return null;
> >end;
> >
> >/Peter
> >
>
> Thanks, that served as the best interim solution. Isn't there a way to
> do that inline?
where
replace(translate(col,'0123456789','9999999999'),'9',null) is null
--
==============================
Connor McDonald
http://www.oracledba.co.uk
"Some days you're the pigeon, some days you're the statue..."
Received on Mon Sep 24 2001 - 12:53:45 CDT
