Re: Conversion from string to number with a default value

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 28 May 2004 18:32:22 GMT
Message-ID: <WALtc.999$hB2.241@nwrdny03.gnilink.net>

"Chandresh Prakash" <chandreshprakash_at_hotpop.com> wrote in message news:476b13c7.0405280957.491b988_at_posting.google.com...
> Hi,
> Does anyone know an sql function to convert a string to a number and if the
> string is not a number then converting to a default numeric value in Oracle.
> For example, something like to_number('234',-1) = 234 and
> to_number('34a',-1) = -1.
>
> Thanks in advance,
> Chandresh

You can create a function ... which goes something like this

create or replace function mynum (p_num in varchar2) return number as
begin

   return to_number(p_num);
exception
  when value_error then return -1;
end;
/

or code it something like this:

SQL> var v varchar2(20)
SQL> exec :v := '-34'

PL/SQL procedure successfully completed.

SQL> select decode(translate(ltrim(:v,'-'),'*0123456789','*'),null,:v,'-1') from dual;

DECODE(TRANSLATE(LTRIM(:V,'-')

---

-34

SQL> exec :v := '-34a'

PL/SQL procedure successfully completed.

SQL> select decode(translate(ltrim(:v,'-'),'*0123456789','*'),null,:v,'-1') from dual;

DECODE(TRANSLATE(LTRIM(:V,'-')

---

-1

Anurag Received on Fri May 28 2004 - 13:32:22 CDT