Re: Leading Zeros

Lines:

zerohold =....
zerohold =...

replace with

zerohold = 'CID' ||lpad(To_Char(newid),5,'0'); --zerohold legth in this case is 5+3=8 chars, change value 5 for actual size

/\/\RLe

"Jerry Davidson" <jerry_davidson_at_compuserve.com> wrote in message news:3C309F3F.3050804_at_compuserve.com...
> 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;
> END trgUSA_Contractor_BI;
>
> Any ideas?
>
> TIA,
> Jerry
>
Received on Fri Jan 04 2002 - 10:41:38 CET