Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1FD036324E2 for ; Mon, 22 Mar 2010 23:16:57 -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 96236-08 for ; Tue, 23 Mar 2010 02:16:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n8.bullet.mail.ac4.yahoo.com (n8.bullet.mail.ac4.yahoo.com [76.13.13.236]) by mail.postgresql.org (Postfix) with SMTP id 5FAA863241A for ; Mon, 22 Mar 2010 23:16:46 -0300 (ADT) Received: from [76.13.13.25] by n8.bullet.mail.ac4.yahoo.com with NNFMP; 23 Mar 2010 02:16:44 -0000 Received: from [67.195.9.82] by t4.bullet.mail.ac4.yahoo.com with NNFMP; 23 Mar 2010 02:16:44 -0000 Received: from [98.137.27.218] by t2.bullet.mail.gq1.yahoo.com with NNFMP; 23 Mar 2010 02:16:43 -0000 Received: from [127.0.0.1] by omp128.mail.gq1.yahoo.com with NNFMP; 23 Mar 2010 02:16:43 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 958874.95443.bm@omp128.mail.gq1.yahoo.com Received: (qmail 10168 invoked by uid 60001); 23 Mar 2010 02:16:43 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1269310603; bh=WssT+zIPu8iqa2xM1vD3jfM479hqwdO/kt8qAuEUs60=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=2MG0/wGQDhyckn39EDIQZgTGcmwPBnAfAkpEpyk2aK8qnZXnwJoNuYJE3BdP37SxA7YQOsLja9evy5pITgDtag0HhqVvrcf3+T79kgdLyLV/FT0F+Ngd7UMCwuZe8A2bPLYRfcpOESKcbpyBPo2OqttB7+uWcXLVyCyMkvCALgo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=K9nfbL1EvabNVXpvx30K6qLdhgcCyLxnt7/HG22L4M+RQ4d/3dXMV35RVwn5rhSltlMRpL1YIZd/NFoOKDdjr9IJpjSaCQG8VrPpdABvxJKBzgs4v15Yu+2JiaZLbo/f0jRW831uGR6ulLXoQCRf0x6+0zyElpWL2wut/51i94M=; Message-ID: <848958.10143.qm@web113702.mail.gq1.yahoo.com> X-YMail-OSG: TRxQ64wVM1nrmIyJz8VhFF_jBzo3LNilpFowZBh4z01QqvO W6v8Du63rbWGqtM0BnwCJajAj6yJHk6R45M0Kf5f4EtqGsZ76xrCcEmLCzjj ipVaPL4Htb04kPLVxeL0o09VeCoB.zmijU8Hzv4EBr1Gc3VXQ.IKMoIVgYz3 DDf3NbR09COQfBEbKD9hwe3EbmJuq9BzMZ6cxYGisCVsk2C6LaORY00jzaLj tjEiubrV227j7AzQT_14UY6hHY_uofUWs1Raw.hLavUZNn.yZqBCHahuQ3rg rKLte6hL.FLXde1xA.d5SEpKV5D0- Received: from [75.162.19.102] by web113702.mail.gq1.yahoo.com via HTTP; Mon, 22 Mar 2010 19:16:43 PDT X-Mailer: YahooMailRC/324.3 YahooMailWebService/0.8.100.260964 References: <22285.35517.qm@web113713.mail.gq1.yahoo.com> <4BA81E52.9090200@venaver.info> <4BA8229E.70108@venaver.info> Date: Mon, 22 Mar 2010 19:16:43 -0700 (PDT) From: Neil Stlyz Subject: Re: string functions and operators To: Petru Ghita , pgsql-sql mailing list In-Reply-To: <4BA8229E.70108@venaver.info> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-303027136-1269310603=:10143" 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/165 X-Sequence-Number: 33872 --0-303027136-1269310603=:10143 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable This is good, however, I need only the numbers to the right of the decimal = point.... =0A=0Aso if my number if 17.2=0A=0AI would need one query that wo= uld return 17=A0=A0 (your function will do that) =0A=0Aand the second query= would return:=A0=A0 2=0A=0Anot 0.2=0A=0Ajust 2=0A=0ADoes that make sense? = =0A=0A=0A=0A=0A________________________________=0AFrom: Petru Ghita =0ATo: Neil Stlyz ; pgsql-sql mailing l= ist =0ASent: Mon, March 22, 2010 8:08:30 PM=0ASub= ject: Re: [SQL] string functions and operators=0A=0A-----BEGIN PGP SIGNED M= ESSAGE----- =0AHash: SHA1 =0A=A0=0AFor numeric data types use:=0A=0Ahttp://= www.postgresql.org/docs/8.4/static/functions-math.html=0A=0AYou could then = use|floor|(dp or numeric)|| for example:=0A=0Apostgres=3D# select floor(71.= 912);=0A=A0floor=0A- -------=0A=A0=A0=A0 71=0A(1 row)=0A=0Apostgres=3D# sel= ect 71.912-floor(71.912);=0A=A0?column?=0A- ----------=0A=A0=A0=A0 0.912=0A= =0A=0ABut as you might have negative numbers in there I guess you should=0A= abs() the values like in:=0A=0Apostgres=3D# select abs(71.912)-floor(abs(71= .912));=0A=A0?column?=0A- ----------=0A=A0=A0=A0 0.912=0A=0A=0Apostgres=3D#= select abs(-171.912)-floor(abs(-171.912));=0A=A0?column?=0A- ----------=0A= =A0=A0=A0 0.912=0A(1 row)=0A=0A=0A=0A=0A=0A=0AOn 23/03/2010 2:50, Petru Ghi= ta wrote:=0A> That field of yours... what type is it? Is it TEXT? is it a n= umeric=0A> type? If it's TEXT, why don't you make it say... NUMERIC(/10/,= =0A> /6///)?=0A> =0A> http://www.postgresql.org/docs/8.4/interactive/dataty= pe-numeric.html#DATATYPE-NUMERIC-DECIMAL=0A>=0A>=0A>=0A> =0AOn 23/03/2010 2= :20, Neil Stlyz wrote:=0A>> Hello,=0A> =0A> =0A> =0A>> I have a dilema and = I was hoping someone here may offer guidance=0A> =0A>> or assistance. I bet= this is a very simple question for someone=0A> =0A>> out there but I am ha= ving problems coming up with a solution.=0A> Here=0A> =0A>> it is...=0A> = =0A> =0A> =0A>> suppose I have a field with the following values:=0A> =0A> = =0A> =0A>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2=0A> =0A> =0A> =0A>> I need t= wo seperate SELECT queries. One would return the=0A> following=0A> =0A>> va= lues (everything left of the decimal point)=0A> =0A> =0A> =0A>> 77 77 134 1= 34 5 5=0A> =0A> =0A> =0A>> The second query would return all of the values = to the right of=0A> =0A>> the decimal point:=0A> =0A> =0A> =0A>> 1 2 1 2 3 = 1 2=0A> =0A> =0A> =0A> =0A> =0A>> Now, I have been using the following info= rmation (although very=0A> =0A>> Greek) to try to solve this problem:=0A> = =0A> =0A> =0A> =0A> http://www.postgresql.org/docs/current/static/functions= -string.html=0A>=0A>=0A>=0A>=0A>=A0 And I have been playing around with the= syntax of the following:=0A> =0A> =0A> =0A>> substring('112.5' from '%#"__= _.#"_' for '#')=0A> =0A> =0A> =0A>> but the aforementioned is not quite wor= king out... can someone=0A> =0A>> please show me a string function that wil= l produce the desired=0A> =0A>> results?=0A> =0A> =0A> =0A>> Thanks! ~n=0A>= =0A> =0A> =0A> =0A-----BEGIN PGP SIGNATURE----- =0AVersion: GnuPG v1.4.9 (= MingW32) =0AComment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/= =0A=A0=0AiEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb = =0AqXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib =0A=3DOY7b =0A-----END PGP SIGNATURE---= -- =0A=0A=0A --0-303027136-1269310603=:10143 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
This is good, however, I need only the numbers to the = right of the decimal point....
=0A
 
=0A
so if my n= umber if 17.2
=0A

I would need one query that would return 17&= nbsp;  (your function will do that)
=0A
 
=0A
= and the second query would return:   2
=0A
 
= =0A
not 0.2
=0A
 
=0A
just 2
=0A
 =
=0A
Does that make sense?
=0A

=0A
=0A
=0AFrom: Petru Ghita <petrutz@venaver.info>
= To: Neil Stlyz <neilstylz@y= ahoo.com>; pgsql-sql mailing list <pgsql-sql@postgresql.org>
Sent: Mon, March 22, 2010 8:0= 8:30 PM
Subject: Re: [SQ= L] string functions and operators

-----BEGIN PGP SIGNED MESSA= GE-----
Hash: SHA1
 
For numeric data types use:

htt= p://www.postgresql.org/docs/8.4/static/functions-math.html

You could= then use|floor|(dp or numeric)|| for example:

postgres=3D# select f= loor(71.912);
 floor
- -------
    71
(1 ro= w)

postgres=3D# select 71.912-floor(71.912);
 ?column?
- = ----------
    0.912


But as you might have neg= ative numbers in there I guess you should
abs() the values like in:

postgres=3D# select abs(71.912)-floor(abs(71.912));=
 ?column?
- ----------
    0.912


p= ostgres=3D# 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 numer= ic
> type? If it's TEXT, why don't you make it say... NUMERIC(/10/,> /6///)?
>
> http://www.postgresql.org/docs/8.4/interact= ive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
><= BR>>
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. O= ne would return the
> following
>
>> values (everythi= ng left of the decimal point)
>
>
>
>> 77 77 = 134 134 5 5
>
>
>
>> The second query would r= eturn all of the values to the right of
>
>> the decimal po= int:
>
>
>
>> 1 2 1 2 3 1 2
>
> =
>
>
>
>> Now, I have been using the followin= g information (although very
>
>> Greek) to try to solve th= is problem:
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>= ;
>
>
>
>  And I have been playing around with= the syntax of the following:
>
>
>
>> substr= ing('112.5' from '%#"___.#"_' for '#')
>
>
>
>&g= t; but the aforementioned is not quite working out... can someone
> <= BR>>> please show me a string function that will produce the desired<= BR>>
>> results?
>
>
>
>> Thanks= ! ~n
>
>
>
>
-----BEGIN PGP SIGNATURE= -----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Moz= illa - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkuoIp4ACgkQt6= IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb
qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib=
=3DOY7b
-----END PGP SIGNATURE-----


= =0A=0A --0-303027136-1269310603=:10143--