RE: Looking for an SQL to append leading zeros

["Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>]

> From your reply, it seems like FIELD2 is a character field.  If so, try
below:

select                               
   substr('0000000000'||trim(field2),
          length(trim(field2))+1,    
          10) as field2              
 from  FILE where FIELD1 IN ('NYC1','NYC2')

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of donna lester
Sent: Thursday, November 09, 2006 12:33 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Looking for an SQL to append leading zeros

This  does nt work for me.
  I want to update FIELD2 with leading zeroes wherever it finds a value
less than 10 digits.

Peter Levy <plevy@xxxxxxxxxxxx> wrote:
  Try:

select digits(FIELD2) from FILE where FIELD1 IN ('NYC1','NYC2')

donna lester wrote:
> Hi
> select FIELD2 from FILE where FIELD1 IN ('NYC1','NYC2')
>
> The result is
> ------------
> 78968
> 5674
> 330
> 376543
> --------------------------------
> FIELD2 is defined as max length of 10 digits. 
> when I run above SQL it gives some rows as shown above where FIELD2
values are less than 10 digits.
> Wherever the value of FIELD2 is less than 10 digits , I want to add
leading zeroes to make it 10 digit number.
> for eg if FIELD2 of above SQL is 78968 I want to make it as 0000078968

> (making it as total 10 digit value).
> The result should be as below
> 0000078968
> 0000005674
> 0000000330
> 0000376543
>
> Greatly appreciate your help...
>
>
> Thanks,
>
> Donna.
>
> ---------------------------------
> Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.




Thanks,

Donna.
 
---------------------------------
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.