Leading Zeros

I want to create a field with leading zeros. What I'm doing is taking a   sequence number and putting a literal at the front. But I want the number to have leading zeros. The result would look like this:

CID001
CID002
...

CID010
CID011
...

CID020
CID021
...

CID100
CID101
...

CID200
CID201 Without the leading zeros, it would sort goofy like this:

CID1
CID10
CID11
CID100
...

CID2
CID20
CID21
CID200
...

I populate this field from a sequence (it currently comes out the goofy way). I'm thinking in terms of something like this:

CREATE OR REPLACE TRIGGER trgUSA_Contractor_BI

   before insert on tblUSA_Contractor for each row    declare

     newid number;
	zerohold char;
   begin
     select seqUSA_Contractor.nextval
       into newid from dual;

     zerohold = Substr('00000000',1, 7 - Length(To_Char(newid)));
     zerohold = 'CID' || zerohold || TO_CHAR(newid);
	
     :new.Contractor_Key := newid;
     :new.Contractor_ID := zerohold;
     :new.Create_Date := sysdate;
     :new.modify_date := sysdate;

Any ideas?

TIA,
Jerry Received on Mon Dec 31 2001 - 18:24:15 CET