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);

SET RetVal = SUBSTR(CHAR(MOD(ABS(X), 1)), 22, D+1);

SET XN = ABS(X);

Main_Loop:

WHILE XN > 0 DO

   SET RetVal = SUBSTR(CHAR(MOD(XN,1000)),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

!

Example A 4 shows the results of the converted FORMAT.

Example: A 4   Converted FORMAT UDF result

                               Command Entered                               

SELECT N

     , FORMAT(N, 2)

     , FORMAT(N, 0)

  FROM (VALUES 12.34567,  12.34567, 120034.567, 123400123456789.) S(N)!

  Return result

N                           2                        3                     

                  12.34567  12.34                    12.                     

                  12.34567   12.34                    12.                    

              120034.56700  120,034.56               120,034.              

338 

MySQL to DB2 UDB Conversion Guide