Re: Concatenate numbers
Date: Wed, 2 Jan 2008 13:12:21 -0800 (PST)
Message-ID: <de01a584-701e-42a7-9c34-8e3c327c188d@l6g2000prm.googlegroups.com>
On Jan 2, 3:38 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 2, 2:30 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jan 2, 2:19 pm, 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
> > []
>
> > > 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?
>
> > Changing the datatype to VARCHAR seems like overkill. Numeric data
> > should be stored in numeric data types. I would suggest using the
> > correct number format for the output. I think
> > set numformat 0.999999999999999999
> > should work. Or using that format pattern with the column command:
> > column digits format 0.999999999999999999
> > should give your desired result.
>
> > (note: I did not test this pattern, but the syntax of the commands is
> > correct.)
>
> > HTH,
> > ed- Hide quoted text -
>
> > - Show quoted text -
>
> I have tested such a 'fix' and it doesn't do what the OP requests.
> The concatenation operation, along with an implicit conversion, 'lops
> off' the leading 0 (again, as it's not a significant digit).
>
> Preserving the leading 0 is, at least in 10.2.0.3 and earlier
> releases, only possible with the data stored as a varchar2.
>
> David Fitzjarrell
I tested it and it does what he wants. There are two problems though. First it inserts a space before the number, second is that you must have a fixed number of digits after the decimal. The first can be remedied using a substr and the second by using a format mask with the maximum number of decimal places then using rtrim to remove the trailing 0's
SQL> select to_char(sysdate,'YYYYMMDD')||','||to_char(0.8,'0.99999') from dual;
TO_CHAR(SYSDATE,'
20080102, 0.80000
SQL> select to_char(sysdate,'yyyymmdd')||','|| rtrim(substr(to_char(0.8,'0.99999'),2),'0') from dual;
TO_CHAR(SYSDATE,'YYYYMMDD')||','||RTR
20080102,0.8 Received on Wed Jan 02 2008 - 15:12:21 CST