RE: Looking for an SQL to append leading zeros

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.