-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 select 0.341*pow(10,length(0.341::text)-2);
2 is a constat that stands for the '0.' part of the string representing the decimal part of the number. Petru Ghita On 23/03/2010 3:16, Neil Stlyz wrote: > This is good, however, I need only the numbers to the right of the > decimal point.... > > so if my number if 17.2 > > I would need one query that would return 17 (your function will > do that) > > and the second query would return: 2 > > not 0.2 > > just 2 > > Does that make sense? > > ---------------------------------------------------------------------- > > > *From:* Petru Ghita <petr...@venaver.info> > *To:* Neil Stlyz <neilst...@yahoo.com>; pgsql-sql mailing list > <pgsql-sql@postgresql.org> *Sent:* Mon, March 22, 2010 8:08:30 PM > *Subject:* Re: [SQL] string functions and operators > > For numeric data types use: > > http://www.postgresql.org/docs/8.4/static/functions-math.html > > You could then use|floor|(dp or numeric)|| for example: > > postgres=# select floor(71.912); floor ------- 71 (1 row) > > postgres=# select 71.912-floor(71.912); ?column? ---------- 0.912 > > > But as you might have negative numbers in there I guess you should > abs() the values like in: > > postgres=# select abs(71.912)-floor(abs(71.912)); ?column? > ---------- 0.912 > > > postgres=# select abs(-171.912)-floor(abs(-171.912)); ?column? > ---------- 0.912 (1 row) > > > > > > > On 23/03/2010 2:50, Petru Ghita wrote: >> That field of yours... what type is it? Is it TEXT? is it a >> numeric type? If it's TEXT, why don't you make it say... >> NUMERIC(/10/, /6///)? > >> > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL > > > > > > > On 23/03/2010 2:20, Neil Stlyz wrote: >>> Hello, > > > >>> I have a dilema and I was hoping someone here may offer >>> guidance > >>> or assistance. I bet this is a very simple question for > someone > >>> out there but I am having problems coming up with a solution. >> Here > >>> it is... > > > >>> suppose I have a field with the following values: > > > >>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2 > > > >>> I need two seperate SELECT queries. One would return the >> following > >>> values (everything left of the decimal point) > > > >>> 77 77 134 134 5 5 > > > >>> The second query would return all of the values to the right >>> of > >>> the decimal point: > > > >>> 1 2 1 2 3 1 2 > > > > > >>> Now, I have been using the following information (although >>> very > >>> Greek) to try to solve this problem: > > > > > > http://www.postgresql.org/docs/current/static/functions-string.html > > > > > > > And I have been playing around with the syntax of the following: > > > >>> substring('112.5' from '%#"___.#"_' for '#') > > > >>> but the aforementioned is not quite working out... can someone > >>> please show me a string function that will produce the desired > >>> results? > > > >>> Thanks! ~n > > > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuoOKoACgkQt6IL6XzynQSVFgCgvUGRoBgCwj2UDa3M9sfF6U3s Jm8AoMTL7Vb9ehj31y3Lv0PaNYV5tJhX =vITl -----END PGP SIGNATURE-----
