How to elide leading zeros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 16 Dec 2005 20:53 GMT

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.

Reply to this Message

Tonkuma - 17 Dec 2005 14:43 GMT

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

||t5.dosage_units

Reply to this Message

Tonkuma - 17 Dec 2005 15:33 GMT

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

Reply to this Message

Bob Stearns - 17 Dec 2005 19:19 GMT

> 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.

Reply to this Message