Re: Can TO_NUMBER Do This?

Ed,

to_number(translate(yourcolumn,'()','-'),'999999.99')

You can use the TRANSLATE function to get the data into a format where a mask will work. There are a gazillion variations on it; but, the one above seems pretty straightforward. Replace the ( with a minus and a ) with a null. This assumes consistency in the why the data is specified, as would most approaches I suppose.

  1* select sort p1,
  2 to_number(translate(sort,'()','-'),'999999.99') p2   3 from oops
SQL> /

P1                      P2
--------------- ----------
(34,989.89)      -34989.89
34,989.89         34989.89
1,234                 1234
(1,234)              -1234

Regards,

L. Elkins

On Mon Aug 28 14:26:27 2000, ORACLE-L_at_fatcity.com 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 - 15:13:24 CDT