Re: function for translating string to number

On Jan 23, 8:34 am, "Eitan M" <no_spam_please_at_nospam_please.com> wrote:
> Hello,
>
> I want to translate string to number,
> but if the string is not a number,
> I want that the result will be some default (can be zero).
>
> to_number('a') run an exception,
> but I am looking for a function that return a default number,
> whether the string doesn't represent a numeric value, please.
>
> Thanks :)

Have you considered writing a user defined function for what you want?

UT1 > set echo on
UT1 > create or replace function isnum(
  2 p_instr in varchar2
  3 ) return number is
  4 --
  5 v_value number := 0;
  6 --
  7 begin
  8 v_value := to_number(p_instr);
  9 return v_value;
 10 exception
 11 when others then
 12 return null;
 13 end;
 14 /

Function created.

UT1 > select isnum(45) as First, isnum('X') as String from sys.dual;

     FIRST STRING
---------- ----------

        45 0

HTH -- Mark D Powell -- Received on Tue Jan 23 2007 - 09:23:27 CST