Re: Concatenate numbers

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 03 Jan 2008 00:15:47 +0100
Message-ID: <477C1B23.9070008@gmail.com>


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.

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 Received on Wed Jan 02 2008 - 17:15:47 CST

Original text of this message