Quote:
The best solution I figured out on how to remove leading zeros when converting Decimal to Char/Varchar in SQL is to concat the integer and the fractional portion of the value as shown in the following (standalone) example:
select concat(concat(rtrim(char(int(val))), '.'),
rtrim(right(char(val - int(val)), 3)))
from (values (10.20), (11), (.02), (.3), (123)) as mytab(val);
1
------
10.20
11.00
0.02
0.30
123.00
5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
|
Although it would be good for positive values,
it would fail for negative values(with non-zero fraction and/or 0 > val > -1).
I already pointed out the issue for 0 > field_value > -1.
Quote:
I wrote:
Quote:
|
I tried to fix the issue(nitya_modified). But, I couldn't do it(minus sign is not displayed for 0 > field_value > -1).
|
|
Here is an example:
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, concat(concat(rtrim(char(int(val))), '.'),
rtrim(right(char(val - int(val)), 3)))
FROM (VALUES (10.20), (11), (.02), (.3), (123)
, (-10.20), (-11), (-.02), (-.3), (-123)
) as mytab(val)
;
------------------------------------------------------------------------------
VAL 2
--------------- ----------------------------
10.20 10.20
11.00 11.00
0.02 0.02
0.30 0.30
123.00 123.00
-10.20 -10..20
-11.00 -11.00
-0.02 0..02
-0.30 0..30
-123.00 -123.00
10 record(s) selected.
By the way, DB2 9.7 for LUW supports VARCHAR_FORMAT(or TO_CHAR). For example:
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, VARCHAR_FORMAT(val)
FROM (VALUES (10.20), (11), (.02), (.3), (123)
, (-10.20), (-11), (-.02), (-.3), (-123)
) as mytab(val)
;
------------------------------------------------------------------------------
VAL 2
--------------- ------------------------------------------
10.20 10.20
11.00 11.00
0.02 0.02
0.30 0.30
123.00 123.00
-10.20 -10.20
-11.00 -11.00
-0.02 -0.02
-0.30 -0.30
-123.00 -123.00
10 record(s) selected.