Re: Converting two numbers into one string

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 17 Apr 2001 06:50:18 +0200
Message-ID: <qmindt4od0iat1soudvbihmlnef0hd5b3q@4ax.com>

On Tue, 17 Apr 2001 11:26:50 +0800, Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote:

>Dear all,
>
>When two numbers are to be converted into one string like this one:
>SELECT 2001 year, 12 month, LTRIM(TO_CHAR(2001,'0000')) ||
>LTRIM(TO_CHAR(12,'00')) yyyymm FROM DUAL;
>
> year month YYYYMM
>--------- --------- --------
> 2001 12 200112
>
>LTRIM has to be prefixed to both number conversions, which makes the
>SQL very time-consuming and the statement look ugly for a simple
>conversion like this. Any possibility to suppress the LTRIM? Thanks.
>
>Dino

Use
select 2001 year, 12 month, to_char(2001,'fm0000')||to_char(12,'fm00') from dual;

In response to your bold and untrue assertion (did you measure it?) that ltrim is very time consuming, my obvious question is: why don't you hardcode 200112 in your statement directly?

Regards,

Sybrand Bakker, Oracle DBA Received on Mon Apr 16 2001 - 23:50:18 CDT