Re: Concatenate numbers

From: <nickli2000_at_gmail.com>
Date: Wed, 2 Jan 2008 11:19:04 -0800 (PST)
Message-ID: <3d3e43c8-d957-4648-bc4f-403f3d9cec05@d4g2000prg.googlegroups.com>


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? Received on Wed Jan 02 2008 - 13:19:04 CST

Original text of this message