Re: TO_NUMBER and NUMBER(n,m)

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/09/25
Message-ID: <342ccf4e.4673896@netnews.worldnet.att.net>#1/1

On 24 Sep 1997 07:29:27 -0700, Jeff Boes (jboes_at_qtm.net) wrote:

<snip>
>A new database includes NUMBER(7,1) and other such fields. I'm writing Pro*C
>code to insert values, which are coming from a mainframe and look like this:
>
>'0000731' -> representing 73.1
>

<snip>

I just tried this, and amazingly the "V" only works one way. I can convert a number TO a character string with an assumed decimal, but not the reverse. For example:

SQL> select to_char(93.9,'99v9') from dual;

TO_C

---

 939

SQL> select to_number ('939','99v9') from dual; ERROR:
ORA-01722: invalid number

Frankly, I'm a bit astounded since assumed decimal places are so common.

Have you thought about just manually placing the decimal in your numeric strings, since you know where they are supposed to go? Something like this:

  1 select to_number (substr('939',1,2) || '.' || substr('939',3,1))
  2* from dual
SQL> / TO_NUMBER(SUBSTR('939',1,2)||'.'||SUBSTR('939',3,1))

---

                                                93.9

Certainly not an elegant solution.

regards,

Jonathan Gennick
gennick_at_worldnet.att.net Received on Thu Sep 25 1997 - 00:00:00 CDT