Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3470563263A for ; Tue, 23 Mar 2010 00:44:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17096-03 for ; Tue, 23 Mar 2010 03:44:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.akn.me (mail.akn.me [188.40.171.129]) by mail.postgresql.org (Postfix) with ESMTP id 17B22632520 for ; Tue, 23 Mar 2010 00:44:19 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.akn.me (Postfix) with ESMTP id C2FAE4881518; Tue, 23 Mar 2010 04:01:52 +0100 (CET) X-Virus-Scanned: amavisd-new at akn.me Received: from mail.akn.me ([127.0.0.1]) by localhost (mail.akn.me [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 1obWkOc8NB51; Tue, 23 Mar 2010 04:01:47 +0100 (CET) Received: from [127.0.0.1] (213.37.99.4.dyn.user.ono.com [213.37.99.4]) by mail.akn.me (Postfix) with ESMTP id 7A2904881517; Tue, 23 Mar 2010 04:01:47 +0100 (CET) Message-ID: <4BA838AA.7010205@venaver.info> Date: Tue, 23 Mar 2010 04:42:34 +0100 From: Petru Ghita User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3 MIME-Version: 1.0 To: Neil Stlyz , pgsql-sql mailing list Subject: Re: string functions and operators References: <22285.35517.qm@web113713.mail.gq1.yahoo.com> <4BA81E52.9090200@venaver.info> <4BA8229E.70108@venaver.info> <848958.10143.qm@web113702.mail.gq1.yahoo.com> In-Reply-To: <848958.10143.qm@web113702.mail.gq1.yahoo.com> X-Enigmail-Version: 1.0.1 Content-Type: multipart/alternative; boundary="------------010509040908080607020209" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/166 X-Sequence-Number: 33873 This is a multi-part message in MIME format. --------------010509040908080607020209 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit -----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 > *To:* Neil Stlyz ; pgsql-sql mailing list > *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----- --------------010509040908080607020209 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit -----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 <petrutz@venaver.info>
> *To:* Neil Stlyz <neilstylz@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-----

--------------010509040908080607020209--