Re: string functions and operators

From: Petru Ghita <petrutz(at)venaver(dot)info>
To: Neil Stlyz <neilstylz(at)yahoo(dot)com>, pgsql-sql mailing list <pgsql-sql(at)postgresql(dot)org>
Subject: Re: string functions and operators
Date: 2010-03-23 02:08:30
Message-ID: 4BA8229E.70108@venaver.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice pgsql-sql

-----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-----

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Stlyz 2010-03-23 02:16:43 Re: string functions and operators
Previous Message Petru Ghita 2010-03-23 01:50:10 Re: string functions and operators

Browse pgsql-novice by date

  From Date Subject
Next Message Neil Stlyz 2010-03-23 02:16:43 Re: string functions and operators
Previous Message Petru Ghita 2010-03-23 01:50:10 Re: string functions and operators

Browse pgsql-sql by date

  From Date Subject
Next Message Neil Stlyz 2010-03-23 02:16:43 Re: string functions and operators
Previous Message Petru Ghita 2010-03-23 01:50:10 Re: string functions and operators