From: Petru Ghita <petrutz@venaver.info>
To: Neil Stlyz <neilstylz@yahoo.com>
To: pgsql-sql mailing list <pgsql-sql@postgresql.org>
Subject: Re: string functions and operators
Date: Tue, 23 Mar 2010 04:42:34 +0100
Message-ID: <4BA838AA.7010205@venaver.info> (raw)
In-Reply-To: <848958.10143.qm@web113702.mail.gq1.yahoo.com>
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>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: petrutz@venaver.info, neilstylz@yahoo.com
Subject: Re: string functions and operators
In-Reply-To: <4BA838AA.7010205@venaver.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox