Chiranjeev Mukherjee wrote:
> I have a Numeric field ARTICLE_NBR of length 9.
> Some of the articles are of length less than 9.
> Suppose mys article no is : 123456
> In my output I need it as : 000123456.

Here's a udf I've written for this.


DROP FUNCTION zeropad
@
CREATE FUNCTION zeropad (arg_val INTEGER, arg_len INTEGER)
RETURNS VARCHAR(20)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
DECLARE arg_val_char VARCHAR(20);
DECLARE arg_val_pad VARCHAR(20);
DECLARE arg_val_len INT;

--METHOD #1:
SET arg_val_char = RIGHT(DIGITS(arg_val),arg_len);

--METHOD #2:
--SET arg_val_len = LENGTH(arg_val_char);
--SET arg_val_pad = CONCAT(LEFT('0000000000',
-- (arg_len - arg_val_len)), arg_val_char);

RETURN arg_val_pad;
END
@


usage:

select zeropad(test_id,9)
from dim_test
fetch first 50 rows only

0000000026
0000000027
0000000028
0000000029
0000000030
0000000031
0000000032
0000000033
0000000034


Regards,

Ken

DB2 & UDB email list listserv db2-l LazyDBA home page