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
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
Moderator: NorbertKrupa
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)
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)
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)
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)