Re: Can TO_NUMBER Do This?

Not pretty but should do the trick

select to_number(replace(replace(replace('(1,123.25)',')'), '(','-'),',')) new_num from dual;

Mike

• 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 2: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
> also send the HELP command for other information (like subscribing).
Received on Mon Aug 28 2000 - 15:16:04 CDT