pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstring functions and operators
9+ messages / 5 participants
[nested] [flat]
* string functions and operators
@ 2010-03-23 01:20 Neil Stlyz <neilstylz@yahoo.com>
0 siblings, 2 replies; 9+ messages in thread
From: Neil Stlyz @ 2010-03-23 01:20 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 01:50 Petru Ghita <petrutz@venaver.info>
parent: Neil Stlyz <neilstylz@yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: Petru Ghita @ 2010-03-23 01:50 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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/
iEYEARECAAYFAkuoHlIACgkQt6IL6XzynQTJ/ACfX4mSteAz9CmZLnPCayz+jXQI
IhoAnA7qrFHNmRVObfSvE+YXZ0OKr3MS
=wvB9
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 02:08 Petru Ghita <petrutz@venaver.info>
parent: Petru Ghita <petrutz@venaver.info>
0 siblings, 1 reply; 9+ messages in thread
From: Petru Ghita @ 2010-03-23 02:08 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; 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-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 02:16 Neil Stlyz <neilstylz@yahoo.com>
parent: Petru Ghita <petrutz@venaver.info>
0 siblings, 2 replies; 9+ messages in thread
From: Neil Stlyz @ 2010-03-23 02:16 UTC (permalink / raw)
To: Petru Ghita <petrutz@venaver.info>; pgsql-sql
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
-----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-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 03:42 Petru Ghita <petrutz@venaver.info>
parent: Neil Stlyz <neilstylz@yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: Petru Ghita @ 2010-03-23 03:42 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 03:49 John R Pierce <pierce@hogranch.com>
parent: Neil Stlyz <neilstylz@yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: John R Pierce @ 2010-03-23 03:49 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
silly me says..
SELECT FLOOR(x), x-FLOOR(x) FROM TABLE;
at least for numeric values.
but, in string space, ummmm...
select split_part(x,'.',1), split_part(x,'.',2) from table;
or
select regexp_replace(x, '\.[0-9]*$',''),
regexp_replace(x,'^[0-9]*\.','') from table;
or god knows how many others.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 03:52 Petru Ghita <petrutz@venaver.info>
parent: Petru Ghita <petrutz@venaver.info>
0 siblings, 1 reply; 9+ messages in thread
From: Petru Ghita @ 2010-03-23 03:52 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 08:13 Andreas Gaab <A.Gaab@scanlab.de>
parent: Petru Ghita <petrutz@venaver.info>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Gaab @ 2010-03-23 08:13 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
Why not using text-function substring:
SELECT split_part(123.456::text,'.',1)::integer;
SELECT split_part(123.456::text,'.',2)::integer;
Regards,
Andreas
-----Ursprüngliche Nachricht-----
Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Petru Ghita
Gesendet: Dienstag, 23. März 2010 04:53
An: Neil Stlyz; pgsql-sql mailing list
Betreff: Re: [SQL] string functions and operators
-----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-----
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: string functions and operators
@ 2010-03-23 14:30 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Neil Stlyz <neilstylz@yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: Little, Douglas @ 2010-03-23 14:30 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; Petru Ghita <petrutz@venaver.info>; pgsql-sql
Here's a thought
create table test1(col1 decimal(7,1));
insert into test1
values(77.1),(77.2),(134.1),(134.2),(134.3),(5.1),(5.2)
select col1::integer from test1;
select substr((col1-col1::integer),3) from test1;
doug
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Neil Stlyz
Sent: Monday, March 22, 2010 9:17 PM
To: Petru Ghita; pgsql-sql mailing list
Subject: Re: [SQL] string functions and operators
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
-----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-----
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-03-23 14:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-23 01:20 string functions and operators Neil Stlyz <neilstylz@yahoo.com>
2010-03-23 01:50 ` Petru Ghita <petrutz@venaver.info>
2010-03-23 02:08 ` Petru Ghita <petrutz@venaver.info>
2010-03-23 02:16 ` Neil Stlyz <neilstylz@yahoo.com>
2010-03-23 03:42 ` Petru Ghita <petrutz@venaver.info>
2010-03-23 03:52 ` Petru Ghita <petrutz@venaver.info>
2010-03-23 08:13 ` Andreas Gaab <A.Gaab@scanlab.de>
2010-03-23 14:30 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-03-23 03:49 ` John R Pierce <pierce@hogranch.com>
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