Re: Can TO_NUMBER Do This?

Others have told how this may be done, so I'm getting on my soapbox...

Maybe some points here you missed when 'enlightening' the developer? Have you tried a slap up the side of the head?
( not advocating that you actually do this. Well, not   very hard anyway )

NEVER store formatting with the data. This does make it rather hard to reformat it in other ways. Does your developer think Oracle is a spreadsheet?

How on earth can math be easily done on such stuff?

Maybe the developer wants to impress others with fancy looking code that appears to do wonderful things?

Simplicity of design is elegant, unnecessary complexity is clumsy and ugly.

OK, I'll get off my soapbox now.

Jared

On Mon, 28 Aug 2000, Ed Bittel 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).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Tue Aug 29 2000 - 09:54:45 CDT