How to elide leading zeros

[Bob Stearns]

I wish to return a dosage (DECIMAL(7,3)) concatenated with its units
(VARCHAR(15); i.e., 1.500 cc. When I do this as shown below, I get
001.500 cc. Is there any easy way to elide the leading zeroes?

CAST(t4.dosage AS CHAR)||' '||t5.dosage_units AS dose,

I get the same result with the CHAR function and, of course,
concatenation does not work on a DECIMAL value. The VARCHAR function
does not accept DECIMAL arguments.

[Tonkuma]

If t4.dosage take only positive values.
TRANSLATE(LTRIM(TRANSLATE(CHAR(t4.dosage),' *','0 ')),'0 ',' *')
||t5.dosage_units

[Tonkuma]

If t4.dosage will take both positive and negative values.
CASE WHEN t4.dosage < 0 THEN '-' ELSE '' END
||TRANSLATE(LTRIM(TRANSLATE(RTRIM(CHAR(t4.dosage)) ,'','0-')),'0',' ')
||' '||t5.dosage_units

[Bob Stearns]

Tonkuma wrote:

If t4.dosage take only positive values.
TRANSLATE(LTRIM(TRANSLATE(CHAR(t4.dosage),' *','0 ')),'0 ',' *')
||t5.dosage_units

Thank you. Very clever use of reversing translates. Something similar,
with two uses of RTRIM could be used to elide trailing zeroes.