strip zeros from fractional part

Did you look at the round function?

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Giovanni M.
Sent: Monday, October 03, 2005 11:45 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] strip zeros from fractional part

Hi guys,

I am searching for a function that enables me to strip the trailing
zeros in the fractional part of a number (numeric type). For example a
number saved in a column of type numeric as such: 23.45000 would be
returned as 23.45

I can't find any function that does this in the documentation, does
something like this exist or do i need to write some custom function
myself?

Thanks for any help,

Giovanni Martina

--
A World of KEIGI
http://keigi.blogspot.com

---------------------------(end of

broadcast)---------------------------

Round and trunc dont provide the functionality I need.

Say for example I have two values in a column of type numeric as follows:
23.455
12.300

What I need to happen is stripping the "useless" zeros in the
fractional part of numbers so 12.300 would become 12.3 and 23.455
would stay the same

Round and trunc can´t do this without me first checking if the number
can indeed be "rounded" to a number without losing its precise value

On 10/3/05, Mike Nolan <nolan@gw.tssi.com> wrote:

Have you looked at the 'round' and 'trunc' functions?

Hi guys,

I am searching for a function that enables me to strip the trailing
zeros in the fractional part of a number (numeric type). For example a
number saved in a column of type numeric as such: 23.45000 would be
returned as 23.45

I can't find any function that does this in the documentation, does
something like this exist or do i need to write some custom function
myself?

Thanks for any help,

Giovanni Martina

--
A World of KEIGI
http://keigi.blogspot.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
A World of KEIGI
http://keigi.blogspot.com

On 10/3/05, Giovanni M. <drayah@gmail.com> wrote:

Round and trunc dont provide the functionality I need.

Say for example I have two values in a column of type numeric as follows:
23.455
12.300

What I need to happen is stripping the "useless" zeros in the
fractional part of numbers so 12.300 would become 12.3 and 23.455
would stay the same

Round and trunc can´t do this without me first checking if the number
can indeed be "rounded" to a number without losing its precise value

As a workaround, you could try using the trim function. You'd need to
cats your numbers to text strings, but it looks like it will drop
useless 0's for you.

test=# SELECT trim(trailing 0 FROM '12.300'::TEXT)::NUMERIC;
rtrim
-------
12.3
(1 row)

Yes! That did it, thanks for the help

On 10/3/05, Tony Wasson <ajwasson@gmail.com> wrote:

On 10/3/05, Giovanni M. <drayah@gmail.com> wrote:

Round and trunc dont provide the functionality I need.

Say for example I have two values in a column of type numeric as follows:
23.455
12.300

What I need to happen is stripping the "useless" zeros in the
fractional part of numbers so 12.300 would become 12.3 and 23.455
would stay the same

Round and trunc can´t do this without me first checking if the number
can indeed be "rounded" to a number without losing its precise value

As a workaround, you could try using the trim function. You'd need to
cats your numbers to text strings, but it looks like it will drop
useless 0's for you.

test=# SELECT trim(trailing 0 FROM '12.300'::TEXT)::NUMERIC;
rtrim
-------
12.3
(1 row)

--
A World of KEIGI
http://keigi.blogspot.com

On Mon, 2005-10-03 at 16:36 -0300, Giovanni M. wrote:

Yes! That did it, thanks for the help

On 10/3/05, Tony Wasson <ajwasson@gmail.com> wrote:

On 10/3/05, Giovanni M. <drayah@gmail.com> wrote:

Round and trunc dont provide the functionality I need.

Say for example I have two values in a column of type numeric as follows:
23.455
12.300

What I need to happen is stripping the "useless" zeros in the
fractional part of numbers so 12.300 would become 12.3 and 23.455
would stay the same

Round and trunc can�t do this without me first checking if the number
can indeed be "rounded" to a number without losing its precise value

As a workaround, you could try using the trim function. You'd need to
cats your numbers to text strings, but it looks like it will drop
useless 0's for you.

test=# SELECT trim(trailing 0 FROM '12.300'::TEXT)::NUMERIC;
rtrim
-------
12.3
(1 row)

--

I also found simply casting the column as a float achieves the same
thing:

test=> select 23.510::numeric;
numeric
---------
23.510

test=> select 23.510::numeric::float;
float8
--------
23.51

Sven