Re: Can TO_NUMBER Do This?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 28 Aug 2000 15:06:57 -0400
Message-Id: <10602.115674@fatcity.com>

The following works:
select to_number(decode(substr(f1, 1, 1), '(', '-' || substr(f1, 2, length(f1) - 2), f1));

for exaple:

SQLWKS> declare f1 varchar2(30);
     2> lOut number;
     3> lOutS varchar2(30);
     4> begin
     5> f1 := '12345.678';
     6> select to_number(decode(substr(f1, 1), '(', '-' || substr(f1, 2,
length(f1) - 2), f1)) INTO lOut FROM dual;
     7> dbms_output.put_line('POSITIVE:');
     8> dbms_output.put_line(lOut);
     9> f1 := '(12345.678)';

    10> select to_number(decode(substr(f1, 1, 1), '(', '-' || substr(f1, 2, length(f1) - 2), f1)) INTO lOut FROM dual;

    11> dbms_output.put_line('NEGATIVE:');
    12> dbms_output.put_line(lOut);
    13> end;
    14> /

Statement processed.
POSITIVE:
12345.678
NEGATIVE:
-12345.678
SQLWKS> Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- From: "Ed Bittel" <ebittel_at_yahoo.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, August 28, 2000 3:26 PM Subject: Can TO_NUMBER Do This?

> Against the repeated urging of the DBA (me), a developer
> insisted on creating all the numeric columns of his new
> appliction's tables as VARCHAR2. (Don't ask me why! I've
> explained it to him several times to no avail.)
>
> Problem
> --------
> Negative values are represented in the tables with parenthesis
> (e.g., '(34,989.89)'). He needs to convert these character
> values to numeric so he can do some math.
>
> I know you can parenthesize values when doing a TO_CHAR like
> this:
> select to_char(f1,'999D99PR') from test where f1=-123.45
>
> TO_CHAR(
> --------
> <123.45>
>
> However, if this doesn't appear to work with the to_number
> function, at least in my case.
>
> Any ideas how to convert at string like '(1,234.87)' to a
> numeric value?
>
> -Ed
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Author: Ed Bittel
> INET: ebittel_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Aug 28 2000 - 14:06:57 CDT