Message-ID: <4BA83B0E.6030109@venaver.info>
Date: Tue, 23 Mar 2010 04:52:46 +0100
From: Petru Ghita <petrutz@venaver.info>
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 <neilstylz@yahoo.com>,
	pgsql-sql mailing list <pgsql-sql@postgresql.org>
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>
	<4BA838AA.7010205@venaver.info>
In-Reply-To: <4BA838AA.7010205@venaver.info>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
For the record if you'd like to use regexp:

select substring('201.123' from $$[0-9]*$$);

and

select substring('201.1232' from $$\.([0-9]*)$$);


On 23/03/2010 4:42, Petru Ghita wrote:
>
> 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/
 
iEYEARECAAYFAkuoOw4ACgkQt6IL6XzynQTnowCgyBRLh7iaJR4sC5Rs2zGgXxXh
vC8An1Yvruvz0IdFF86dN5bQUIESmv8m
=TUxh
-----END PGP SIGNATURE-----