Re: Can TO_NUMBER Do This?

Use REPLACE to replace the '(' with a '-' and the ')' with a NULL, and nest those inside the to_number like so:

to_number(replace(replace(fl,'(','-'),')',NULL))

This works fine in Oracle 8.0.5 on HP-UX 11.0

Kent

At 11:26 AM 8/28/00 -0800, you wrote:
>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 - 13:49:22 CDT