Re: function for translating string to number

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 23 Jan 2007 20:04:27 +0100
Message-ID: <45B65C3B.30101@gmail.com>

Mark D Powell schrieb:
>
> 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 --
>

Or (on 10g) you can use regexp_ functions for that:

SQL> with t as (

   2          select '1' a_number from dual
   3          union all
   4          select 'X' from dual
   5          union all
   6          select '2x' from dual
   7          union all
   8          select '-25' from dual
   9          union all
  10          select '-' from dual

  11 )
  12 select
  13 to_number(
  14
nvl(regexp_substr(a_number,'^[-][[:digit:]]+|[[:digit:]]*$'),0)   15 ) the_number
  16 from t;

THE_NUMBER

---

          1
          0
          0
        -25
          0

Best regards

Maxim Received on Tue Jan 23 2007 - 13:04:27 CST