Re: Concatenate numbers

From: <fitzjarrell_at_cox.net>
Date: Wed, 2 Jan 2008 12:10:27 -0800 (PST)
Message-ID: <5b702645-4d2a-46a3-a34d-0686cb2cf740@d4g2000prg.googlegroups.com>

nickli2..._at_gmail.com wrote:
> On Jan 2, 1:49 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > On Jan 2, 11:38 am, nickli2..._at_gmail.com wrote:
> >
> >
> >
> > > Hi,
> >
> > > I have a table with the following two columns and sample data:
> >
> > > tran_date DATE;
> > > digits NUMBER;
> >
> > > 10/2/2007 0.000738889791980111
> > > 11/22/2007 0.00083740843091
> > > 12/11/2007 6.00083740843091
> >
> > > For a special output, I tried to concatenate the two columns using
> > > the following SQL:
> >
> > > SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits from
> > > mytable;
> >
> > > The result is missing the "0" for two numbers starting with "0."
> > > as in the following:
> >
> > > 20071002,.000738889791980111
> > > 20071122,.00083740843091
> > > 20071211,6.00083740843091 (This is fine)
> >
> > > I know I may be able to do some decode or other manipulation, but
> > > could someone tell me why the leading "0" is omitted from the query
> > > output?
> >
> > > Thanks in advance.
> >
> > > Nick
> >
> > Because the leading 0 to the left of the decimal point is not a
> > significant digit, thus it can be removed without affecting the actual
> > numeric value. If you really need that value stored in the database
> > it may be necessary to change the column definition to that of a
> > varchar2(30):
> >
> > SQL> create table mytable(
> > 2 tran_date date,
> > 3 digits number
> > 4 );
> >
> > Table created.
> >
> > SQL>
> > SQL> insert all
> > 2 into mytable
> > 3 values (to_date('10/02/2007','MM/DD/YYYY'), 0.000738889791980111)
> > 4 into mytable
> > 5 values (to_date('11/22/2007','MM/DD/YYYY'), 0.00083740843091)
> > 6 into mytable
> > 7 values (to_date('12/11/2007','MM/DD/YYYY'), 6.00083740843091)
> > 8 select * From dual;
> >
> > 3 rows created.
> >
> > SQL>
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL>
> > SQL> SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits
> > 2 from mytable;
> >
> > TO_CHAR(TRAN_DATE,'YYYYMMDD')||','||DIGITS
> > -------------------------------------------------
> > 20071002,.000738889791980111
> > 20071122,.00083740843091
> > 20071211,6.00083740843091
> >
> > SQL>
> > SQL> drop table mytable;
> >
> > Table dropped.
> >
> > SQL>
> > SQL> create table mytable(
> > 2 tran_date date,
> > 3 digits varchar2(30)
> > 4 );
> >
> > Table created.
> >
> > SQL>
> > SQL> insert all
> > 2 into mytable
> > 3 values (to_date('10/02/2007','MM/DD/YYYY'),
> > '0.000738889791980111')
> > 4 into mytable
> > 5 values (to_date('11/22/2007','MM/DD/YYYY'), '0.00083740843091')
> > 6 into mytable
> > 7 values (to_date('12/11/2007','MM/DD/YYYY'), '6.00083740843091')
> > 8 select * From dual;
> >
> > 3 rows created.
> >
> > SQL>
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL>
> > SQL> SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits
> > 2 from mytable;
> >
> > TO_CHAR(TRAN_DATE,'YYYYMMDD')||','||DIG
> > ---------------------------------------
> > 20071002,0.000738889791980111
> > 20071122,0.00083740843091
> > 20071211,6.00083740843091
> >
> > SQL>
> >
> > David Fitzjarrell

>

> Thanks for your help. Could you tell me why Oracle decides to omit the
> leading "0" when concatenating the numbers and what settings affect
> this?

NO settings affect this and I've already informed you as to WHY. You can scroll up and read the explanation again.

David Fitzjarrell Received on Wed Jan 02 2008 - 14:10:27 CST

Original text of this message