Page 1 of 1
TRIM trailing zeroes from decimal values.
Posted: Sat Sep 21, 2013 1:12 pm
by pk132007
I have a requirement where i need to remove the trailing zeroes from the decimal values. But in case there is no digit after decimal then a single zero should come.
Example :
0.0000 => 0.0
123.45600 => 123.456
27.0000 => 27.0
I have tried using TRIM and other cast functions, but nothing worked out.
SELECT TO_NUMBER(TO_CHAR(0.0000));
SELECT TRIM(TRAILING '0' FROM TO_CHAR(0.0000));
Can anyone please help me
Re: TRIM trailing zeroes from decimal values.
Posted: Sat Sep 21, 2013 6:21 pm
by id10t
Hi!
But do you understand that it will be a STRING data type?
Code: Select all
daniel=> select col, regexp_substr(col::varchar, '\d+\.(0|[^0]+)') from pk132007;
col | regexp_substr
----------+---------------
0.0000 | 0.0
27.0000 | 27.0
123.4560 | 123.456
(3 rows)
Re: TRIM trailing zeroes from decimal values.
Posted: Sat Sep 21, 2013 6:35 pm
by Julie
Here's another fun way if you are like me and don't like/understand regular expressions
Code: Select all
dbadmin=> \d t
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+---------------+------+---------+----------+-------------+-------------
public | t | c | numeric(25,5) | 16 | | f | f |
(1 row)
dbadmin=> select c, decode(mod(c, 1), 0, to_char(c, 'FM9999999990.0')::varchar, rtrim(c::varchar, '0')) from t;
c | my_format
-----------+-----------
0.00000 | 0.0
123.45600 | 123.456
27.00000 | 27.0
(3 rows)
Although, sKwa's suggestion is much more elegant!
Re: TRIM trailing zeroes from decimal values.
Posted: Sat Sep 21, 2013 8:09 pm
by id10t
Hi!
I like your solution (much more better than my - regexp), you just perplexed a little, but your solution is PERFECT (I forget about TO_CHAR):
Code: Select all
daniel=> select d, to_char(d, 'FM9999999990.0999') from pk132007 ;
d | to_char
----------+---------
0.0000 | 0.0
27.0000 | 27.0
123.4560 | 123.456
(3 rows)
@pk132007
My previous solution buggy, so use in solution of Julie:
Code: Select all
daniel=> select d, regexp_substr(d::varchar, '\d+\.(0|[^0]+)'), to_char(d, 'FM9999999990.0999') from pk132007;
d | regexp_substr | to_char
----------+---------------+---------
0.0000 | 0.0 | 0.0
27.0000 | 27.0 | 27.0
123.4560 | 123.456 | 123.456
12.0500 | 12.0 | 12.05
(4 rows)
Re: TRIM trailing zeroes from decimal values.
Posted: Wed Sep 25, 2013 4:04 am
by pk132007
Thanks Julie and sKwa.
This is what i was looking for.