Re: Odd results from TO

From: Matthias Gresz <GreMa_at_t-online.de>
Date: Tue, 26 Jan 1999 07:26:49 +0100
Message-ID: <36AD6029.3D104E8A@Privat.Post.DE>

pbolduc_at_online-can.com schrieb:
>
> Why does this happen:
>
> select 'x'||to_char(1,'00')||'x' from dual;
>
> returns: 'x 01x'
>
> I would have expected: 'x01x'
>
> I know I could do: select 'x'||ltrim(to_char(1,'00'))||'x' from dual;
> to get the same result but it seems convoluted.
>
> Oracle7 Server Release 7.3.2.3.0 - Production Release
> PL/SQL Release 2.3.2.3.0 - Production
> CORE Version 3.5.2.0.0 - Production
> TNS for HPUX: Version 2.3.2.1.0 - Production
> NLSRTL Version 3.2.2.0.0 - Production
>
> Phil Bolduc
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi,

it's normal documented behavier:

>From oracle docs:

Syntax TO_CHAR(n [, fmt [, 'nlsparams'] ])

Purpose

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2
value exactly long enough to hold its significant digits. For information on number formats, see the section "Format Models" .

If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.

Example I

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount"

FROM DUAL
Amount

$10,000.00-

Note how the output above is blank padded to the left of the currency symbol.

Example II

SELECT TO_CHAR(-10000,'L99G999D99MI',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''AusDollars'' ') "Amount"
        FROM DUAL

Amount

AusDollars10.000,00-
HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Tue Jan 26 1999 - 00:26:49 CST

Re: Odd results from TO_CHAR()