23400123456789.00000  123,400,123,456,789.00   123,400,123,456,789. 

  4 record(s) selected.

A.3  Sample code for RPAD and LPAD functions

This section provides UDF for LPD and RPAD functions. Example A 5 shows 

code for a user defined function emulating RPAD.

Example: A 5   CREATE FUNCTION RPAD and sample usage

   DB2 UDB UDF(User Defined Function) Samples for Migration

   2001/08/27, 09/27, 11/06

   Name of UDFs: RPAD (C1 VarChar(4000), N integer, C2 VarChar(4000))

                 RPAD (I1 Integer,       N integer, C2 Varchar(4000))

                 LPAD (C1 VarChar(4000), N integer, C2 Varchar(4000))

                 LPAD (I1 Integer,       N integer, C2 Varchar(4000))

   Used UDF: None

   Description: Add repeatedly C2 to the right(RPAD) or left(LPAD) of parameter 

1 (C1 or I1)

                and return N byte.

   Author: TOKUNAGA, Takashi

CREATE FUNCTION RPAD (C1 VarChar(4000), N integer, C2 VarChar(4000))

 RETURNS VARCHAR(4000)

 LANGUAGE SQL

 SPECIFIC RPADBase

 DETERMINISTIC

 CONTAINS SQL

 NO EXTERNAL ACTION

 RETURN 

 substr(C1 || 

repeat(C2,((sign(N length(C1))+1)/2)*(N length(C1)+length(C2))/(length(C2)+1 si

gn(length(C2)))),1,N)

;

Example A 6 shows the results of the converted RPAD function.

 Appendix A. Sample code for user defined functions 

339