Hi Grant,

Not a very scalable solution, but if it works for you ....

Personally I would use the LPAD in ib_sql.

DECLARE EXTERNAL FUNCTION lpad
CSTRING(255), INTEGER, CSTRING(1)
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';

Then your SP is like 1 line:

New_No_TX = LPAD(Cast(:New_No as varchar(20)),5);

The other solutions are pretty clever though and avoid using UDFs
(even though ib_sql is distributed with Firebird).

Adam





--- In firebird-support@yahoogroups.com, Grant Brown <grant@s...>
wrote:

> Hi all,
>
> The solution that I finally used was really simple
>
> /* format the new document number */
>
> if (New_No<10) then New_No_Tx = '0000'|| cast(New_No as VarChar

(20));

> else if (New_No<100) then New_No_Tx = '000'|| cast(New_No as
> VarChar(20));
> else if (New_No<1000) then New_No_Tx = '00'|| cast(New_No as
> VarChar(20));
> else if (New_No<10000) then New_No_Tx = '0'|| cast(New_No as
> VarChar(20));
> else New_No_Tx = cast(New_No as VarChar(20));
>
> works for me.
>
> --
> Regards,
> Grant Brown
>
> Product Development Manager
> Phone : 02 4229 1185
> Mobile : 0412 926 995
> Email : grant@s...
> Web : www.sitedoc.com.au
>
> SiteDoc - Easy to Use - Powerful Results