How to trim leading zeroes when converting decimal() to char()

[Jean-michel]

I need to convert a decimal field to char() but also trim the leading
zeroes. Any idea? I could not find any function to do that.

[Blair Adamache]

I'm sure there's a better way, but:

with sample table testdec:

create table testdec (col1 dec(10,5))

some sample data:

insert into testdec values (0005.500),(50. 5),(12345.12345 )

We then take a substring on a char of the first 5 characters (the
precision of the decimal value), convert it into an int to drop the
leading zeros, convert it back into a char, and then perform a right
trim (rtrim) on it to get rid of the padded blanks. We concatenate this
with the substring of the decimal separator and the digits to the right
of the decimal point (the scale), and convert this into a char.

select rtrim
( char(int(substr (char(col1),1,5 ))) )
concat
substr(char(col 1),6,6) from testdec

1
-----------------
5.50000
50.50000
12345.12345

Like any example I provide, please test extensively with boundary
conditions.

Jean-michel wrote:

I need to convert a decimal field to char() but also trim the leading
zeroes. Any idea? I could not find any function to do that.