Обсуждение: 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
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)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
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