By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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(col1),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.


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.