Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 61EE9632270 for ; Mon, 22 Mar 2010 23:10:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 79192-04 for ; Tue, 23 Mar 2010 02:10:07 +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 CDD046321C4 for ; Mon, 22 Mar 2010 23:10:15 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.akn.me (Postfix) with ESMTP id 572E24881518; Tue, 23 Mar 2010 02:27:48 +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 xlkSigtohglQ; Tue, 23 Mar 2010 02:27:43 +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 1A6E94881517; Tue, 23 Mar 2010 02:27:43 +0100 (CET) Message-ID: <4BA8229E.70108@venaver.info> Date: Tue, 23 Mar 2010 03:08:30 +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> In-Reply-To: <4BA81E52.9090200@venaver.info> X-Enigmail-Version: 1.0.1 Content-Type: multipart/alternative; boundary="------------040003090903080501030705" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.165 tagged_above=-10 required=5 tests=AWL=0.433, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/164 X-Sequence-Number: 33871 This is a multi-part message in MIME format. --------------040003090903080501030705 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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/ iEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib =OY7b -----END PGP SIGNATURE----- --------------040003090903080501030705 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
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/
 
iEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb
qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib
=OY7b
-----END PGP SIGNATURE-----

--------------040003090903080501030705--