TRIM trailing zeroes from decimal values.

Moderator: NorbertKrupa

Post Reply
pk132007
Newbie
Newbie
Posts: 7
Joined: Fri Sep 20, 2013 9:05 am

TRIM trailing zeroes from decimal values.

Post by pk132007 » Sat Sep 21, 2013 1:12 pm

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 :roll:

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: TRIM trailing zeroes from decimal values.

Post by id10t » Sat Sep 21, 2013 6:21 pm

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)

User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

Re: TRIM trailing zeroes from decimal values.

Post by Julie » Sat Sep 21, 2013 6:35 pm

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!
Thanks,
Juliette

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: TRIM trailing zeroes from decimal values.

Post by id10t » Sat Sep 21, 2013 8:09 pm

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)
UPDATE
@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)

pk132007
Newbie
Newbie
Posts: 7
Joined: Fri Sep 20, 2013 9:05 am

Re: TRIM trailing zeroes from decimal values.

Post by pk132007 » Wed Sep 25, 2013 4:04 am

Thanks Julie and sKwa.
This is what i was looking for. :)

Post Reply

Return to “Vertica SQL Functions”