Re: Concatenate numbers
Date: Thu, 3 Jan 2008 04:54:22 -0800 (PST)
Message-ID: <e9286c51-4642-4066-834a-cdaeb7466585@5g2000hsg.googlegroups.com>
On Jan 2, 6:15 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Ken Denny schrieb:
>
>
>
>
>
> > 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.
>
> You tested another workaround.
You're right. I didn't read Ed's reply carefully.
>
> 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
>
> Look up in the SQL Reference about 'FM' format modifier.
>
> SQL> select to_char(sysdate,'yyyymmdd')||','||to_char(0.8,'fm0.99999')
> from dual;
>
> TO_CHAR(SYSDATE,'
> -----------------
> 20080103,0.8
>
> Best regards
>
> Maxim
Thanks Maxim. I haven't used the 'FM' before. Glad to know about it. Received on Thu Jan 03 2008 - 06:54:22 CST