Mysql에서 사용하는 format함수를 db2에서 migration하기 위해 UDF함수로 구현한 것입니다.  IBM JAPAN에 있는 분이 만드신 것인데 그대로 하면 함수 생성이 안되어서 약간 수정하였습니다. 참고하세요 ^^

--
-- DB2 UDB UDF(User-Defined Function) Samples for Migration
--
-- Created: 2004/03/08
--
-- Name of UDF: FORMAT (X Decimal(31,10), D Integer)
--
-- Used UDF: None
--
-- Description: Returns rounded to the precision specified by D and a "," for each 3 digits as a separator.
--              If D is 0, the result will have no decimal point or fractional part.
--
-- Author: TOKUNAGA, Takashi
--

------------------------------ Command Entered ------------------------------
CREATE FUNCTION FORMAT (X Decimal(31,10), D Integer)
RETURNS VARCHAR(50)
LANGUAGE SQL
SPECIFIC FORMAT_MySQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
DECLARE XN     DECIMAL(21,0);
DECLARE RetVal VARCHAR(50);

IF D > 0 THEN
   SET RetVal = SUBSTR(CHAR(ABS(X)+0.5*DECIMAL(POWER(0.1,D),11,10)),21, D+1);
ELSE
   SET RetVal = '';
END IF;

SET XN = ABS(X)+0.5*DECIMAL(POWER(0.1,D),11,10);
Main_Loop:
WHILE XN > 0 DO
--   SET RetVal = SUBSTR(CHAR(MOD(XN,1000)),19,3) || RetVal;
   SET RetVal = SUBSTR(CHAR(dec(MOD(bigint(XN),1000),21,0)),19,3) || RetVal;
   SET XN = XN/1000;
   IF XN > 0 THEN
      SET RetVal = ',' || RetVal;
   ELSE
      LEAVE Main_Loop;
   END IF;
END WHILE;

RETURN CASE WHEN X < 0 THEN '-' ELSE '' END
       || TRANSLATE(LTRIM(TRANSLATE(RetVal,' ','0')),'0',' ');
END!

select format(123456,0) from sysibm.sysdummy1

1
---------------------------------------------
123,456