Re: Concatenate numbers

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

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)

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 Received on Wed Jan 02 2008 - 12:49:25 CST