DB2, conversion from DECIMAL value to VARCHAR (without zero)

You can perform this conversion using the following syntax:
REPLACE( REPLACE( rtrim( ltrim( REPLACE( char(  ), '0', ' '))), ' ', '0'), '.', '')

In fact, having the following sample table:
DESCRIBE TABLE pippo

 

COLUMN                         Type      Type

name                           schema    name               Length   Scale Nulls

------------------------------ --------- ------------------ -------- ----- ------

IDPIPPO                        SYSIBM    DECIMAL                  18     0 Yes

CNUMPIPPO                      SYSIBM    VARCHAR                 120     0 Yes

 

  2 record(s) selected.

with a single tuple:

INSERT INTO pippo VALUES (250,NULL);

we can see the difference between a simple cast and the syntax shown above:
UPDATE pippo SET CNUMPIPPO = char(idpippo);
 
IDPIPPO    CNUMPIPPO
250    000000000000000250.

and with some optimizations:
UPDATE pippo SET CNUMPIPPO=REPLACE( REPLACE( rtrim( ltrim( REPLACE( char(idpippo), '0', ' '))), ' ', '0'), '.', '');
 
IDPIPPO    CNUMPIPPO
250    250

NB: syntax is not used when there are negative values.