I have been trying to remove trailing zeroes from a numeric column after the decimal. For example:
0.978219150000 -> 0.97821915
0.650502591918 -> 0.650502591918
0.975479450000 -> 0.97547945
The data type is NUMBER(38,12). Is there any way to remove the trailing zeroes as I mentioned above?
You can try to cast to float:
create or replace table test (a NUMBER(38,12));
insert into test values (0.97821915), (0.650502591918), (0.975479450000);
select a from test;
+----------------+
| A |
|----------------|
| 0.978219150000 |
| 0.650502591918 |
| 0.975479450000 |
+----------------+
select a::float from test;
+--------------+
| A::FLOAT |
|--------------|
| 0.97821915 |
| 0.6505025919 |
| 0.97547945 |
+--------------+
However, depending what you want to achieve, using floating number might not be a good idea due to potential rounding issues.
UPDATE:
I tried the regexp version, not sure if I missed any test case or not:
create or replace table test (a NUMBER(38,12));
insert into test values
(0.97),
(0.650502591918),
(0.975479450000),
(10000),
(1450000),
(12.2000),
(14.0200);
select regexp_replace(
a::varchar,
'^([0-9]+)$|' ||
'^([0-9]+)\.0*$|' ||
'^([0-9]+\.[0-9]{1,}[1-9])0*$|' ||
'^([0-9]+\.[1-9])0*$', '\\1\\2\\3\\4'
) as a from test;
+----------------+
| A |
|----------------|
| 0.97 |
| 0.650502591918 |
| 0.97547945 |
| 10000 |
| 1450000 |
| 12.2 |
| 14.02 |
+----------------+
Where:
^([0-9]+)$ -> will cover the integer like 10000
^([0-9]+)\.0*$ -> will cover integer like 10.000000
^([0-9]+\.[0-9]{1,}[1-9])0*$ -> will cover 14.0200000
^([0-9]+\.[1-9])0*$. -> will cover 12.20000 or 0.97540000
If this is just a formatting/display issue, you can use the to_varchar() function with a fixed decimal format string:
select 123.45::number(38,12); -- 123.450000000000
select to_varchar(123.45::number(38,12), '99G999G999G999G999G999G999G999G999D999999999999'); -- 123.45
Since the format string is a bit long, it may make sense to put it in a UDF to make it more compact in SQL:
create or replace function DISPLAY_38_12(N number(38,12))
returns varchar
language sql
as
$$
to_varchar(123.45::number(38,12), '99G999G999G999G999G999G999G999G999D999999999999')
$$;
select DISPLAY_38_12(123.45::number(38,12));