Thread: string functions and operators
2
-----BEGIN PGP SIGNED MESSAGE----- <br /> Hash: SHA1 <br /> <br /> That field of yours... what type is it?<br /> Is itTEXT? is it a numeric type?<br /> If it's TEXT, why don't you make it say... NUMERIC(/10/, /6///)?<br /><br /><a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL">http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL</a><br /><br/><br /> On 23/03/2010 2:20, Neil Stlyz wrote:<br /><span style="white-space: pre;">> Hello,<br /> > <br /> >I have a dilema and I was hoping someone here may offer guidance<br /> > or assistance. I bet this is a very simplequestion for someone<br /> > out there but I am having problems coming up with a solution. Here<br /> > it is...<br/> > <br /> > suppose I have a field with the following values:<br /> > <br /> > 77.1 77.2 134.1 134.2134.3 5.1 5.2<br /> > <br /> > I need two seperate SELECT queries. One would return the following <br /> >values (everything left of the decimal point)<br /> > <br /> > 77 77 134 134 5 5<br /> > <br /> > The secondquery would return all of the values to the right of<br /> > the decimal point:<br /> > <br /> > 1 2 1 2 31 2<br /> > <br /> > <br /> > Now, I have been using the following information (although very <br /> > Greek)to try to solve this problem:<br /> > <br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/functions-string.html">http://www.postgresql.org/docs/current/static/functions-string.html</a><br />><br /> > And I have been playing around with the syntax of the following:<br /> > <br /> > substring('112.5'from '%#"___.#"_' for '#')<br /> > <br /> > but the aforementioned is not quite working out... cansomeone <br /> > please show me a string function that will produce the desired<br /> > results?<br /> > <br/> > Thanks! ~n<br /> > <br /> > </span><br /> -----BEGIN PGP SIGNATURE----- <br /> Version: GnuPG v1.4.9 (MingW32)<br /> Comment: Using GnuPG with Mozilla - <a class="moz-txt-link-freetext" href="http://enigmail.mozdev.org/">http://enigmail.mozdev.org/</a><br/> <br /> iEYEARECAAYFAkuoHlIACgkQt6IL6XzynQTJ/ACfX4mSteAz9CmZLnPCayz+jXQI<br /> IhoAnA7qrFHNmRVObfSvE+YXZ0OKr3MS <br /> =wvB9 <br/> -----END PGP SIGNATURE----- <br /><br />
-----BEGIN PGP SIGNED MESSAGE----- <br /> Hash: SHA1 <br /> <br /> For numeric data types use:<br /><br /><a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/static/functions-math.html">http://www.postgresql.org/docs/8.4/static/functions-math.html</a><br /><br/> You could then use|floor|(dp or numeric)|| for example:<br /><br /> postgres=# select floor(71.912);<br /> floor<br/> - -------<br /> 71<br /> (1 row)<br /><br /> postgres=# select 71.912-floor(71.912);<br /> ?column?<br />- ----------<br /> 0.912<br /><br /><br /> But as you might have negative numbers in there I guess you should<br />abs() the values like in:<br /><br /> postgres=# select abs(71.912)-floor(abs(71.912));<br /> ?column?<br /> - ----------<br/> 0.912<br /><br /><br /> postgres=# select abs(-171.912)-floor(abs(-171.912));<br /> ?column?<br /> -----------<br /> 0.912<br /> (1 row)<br /><br /><br /><br /><br /><br /><br /> On 23/03/2010 2:50, Petru Ghita wrote:<br/><span style="white-space: pre;">> That field of yours... what type is it? Is it TEXT? is it a numeric<br />> type? If it's TEXT, why don't you make it say... NUMERIC(/10/,<br /> > /6///)?<br /> > <br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL">http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL</a><br />><br /> ><br /> ><br /> > </span><br /> On 23/03/2010 2:20, Neil Stlyz wrote:<br /><span style="white-space:pre;">>> Hello,<br /> > <br /> > <br /> > <br /> >> I have a dilema and I was hopingsomeone here may offer guidance<br /> > <br /> >> or assistance. I bet this is a very simple question forsomeone<br /> > <br /> >> out there but I am having problems coming up with a solution.<br /> > Here<br />> <br /> >> it is...<br /> > <br /> > <br /> > <br /> >> suppose I have a field with the followingvalues:<br /> > <br /> > <br /> > <br /> >> 77.1 77.2 134.1 134.2 134.3 5.1 5.2<br /> > <br />> <br /> > <br /> >> I need two seperate SELECT queries. One would return the<br /> > following<br /> ><br /> >> values (everything left of the decimal point)<br /> > <br /> > <br /> > <br /> >> 77 77134 134 5 5<br /> > <br /> > <br /> > <br /> >> The second query would return all of the values to the rightof<br /> > <br /> >> the decimal point:<br /> > <br /> > <br /> > <br /> >> 1 2 1 2 3 1 2<br/> > <br /> > <br /> > <br /> > <br /> > <br /> >> Now, I have been using the following information(although very<br /> > <br /> >> Greek) to try to solve this problem:<br /> > <br /> > <br /> ><br /> > <br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/functions-string.html">http://www.postgresql.org/docs/current/static/functions-string.html</a><br />><br /> ><br /> ><br /> ><br /> > And I have been playing around with the syntax of the following:<br />> <br /> > <br /> > <br /> >> substring('112.5' from '%#"___.#"_' for '#')<br /> > <br /> > <br />> <br /> >> but the aforementioned is not quite working out... can someone<br /> > <br /> >> please showme a string function that will produce the desired<br /> > <br /> >> results?<br /> > <br /> > <br />> <br /> >> Thanks! ~n<br /> > <br /> > <br /> > <br /> > </span><br /> -----BEGIN PGP SIGNATURE-----<br /> Version: GnuPG v1.4.9 (MingW32) <br /> Comment: Using GnuPG with Mozilla - <a class="moz-txt-link-freetext"href="http://enigmail.mozdev.org/">http://enigmail.mozdev.org/</a><br /> <br /> iEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb<br /> qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib <br /> =OY7b <br/> -----END PGP SIGNATURE----- <br /><br />
I would need one query that would return 17 (your function will do that)
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-----
-----BEGIN PGP SIGNED MESSAGE----- <br /> Hash: SHA1 <br /> <br /><br /> select 0.341*pow(10,length(0.341::text)-2);<br/><br /> 2 is a constat that stands for the '0.' part of the string<br /> representingthe decimal part of the number.<br /><br /><br /> Petru Ghita<br /><br /> On 23/03/2010 3:16, Neil Stlyz wrote:<br/><span style="white-space: pre;">> This is good, however, I need only the numbers to the right of the<br />> decimal point....<br /> > <br /> > so if my number if 17.2<br /> > <br /> > I would need one query thatwould return 17 (your function will <br /> > do that)<br /> > <br /> > and the second query would return: 2<br /> > <br /> > not 0.2<br /> > <br /> > just 2<br /> > <br /> > Does that make sense?<br />> <br /> > ----------------------------------------------------------------------<br /> ><br /> ><br /> ></span><br /> *From:* Petru Ghita <a class="moz-txt-link-rfc2396E" href="mailto:petrutz@venaver.info"><petrutz@venaver.info></a><br/><span style="white-space: pre;">> *To:* Neil Stlyz<a class="moz-txt-link-rfc2396E" href="mailto:neilstylz@yahoo.com"><neilstylz@yahoo.com></a>; pgsql-sql mailinglist <br /> > <a class="moz-txt-link-rfc2396E" href="mailto:pgsql-sql@postgresql.org"><pgsql-sql@postgresql.org></a>*Sent:* Mon, March 22, 2010 8:08:30 PM <br />> *Subject:* Re: [SQL] string functions and operators<br /> > <br /> > For numeric data types use:<br /> ><br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/static/functions-math.html">http://www.postgresql.org/docs/8.4/static/functions-math.html</a><br />> <br /> > You could then use|floor|(dp or numeric)|| for example:<br /> > <br /> > postgres=# select floor(71.912);floor ------- 71 (1 row)<br /> > <br /> > postgres=# select 71.912-floor(71.912); ?column? ----------0.912<br /> > <br /> > <br /> > But as you might have negative numbers in there I guess you should<br/> > abs() the values like in:<br /> > <br /> > postgres=# select abs(71.912)-floor(abs(71.912)); ?column?<br /> > ---------- 0.912<br /> > <br /> > <br /> > postgres=# select abs(-171.912)-floor(abs(-171.912));?column? <br /> > ---------- 0.912 (1 row)<br /> > <br /> > <br /> > <br />> <br /> > <br /> > <br /> > On 23/03/2010 2:50, Petru Ghita wrote:<br /> >> That field of yours... whattype is it? Is it TEXT? is it a <br /> >> numeric type? If it's TEXT, why don't you make it say... <br /> >>NUMERIC(/10/, /6///)?<br /> > <br /> >> <br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL">http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL</a><br />><br /> ><br /> ><br /> ><br /> ><br /> ><br /> > </span><br /> On 23/03/2010 2:20, Neil Stlyz wrote:<br/><span style="white-space: pre;">>>> Hello,<br /> > <br /> > <br /> > <br /> >>> I havea dilema and I was hoping someone here may offer <br /> >>> guidance<br /> > <br /> >>> or assistance.I bet this is a very simple question for<br /> > someone<br /> > <br /> >>> out there but I amhaving problems coming up with a solution.<br /> >> Here<br /> > <br /> >>> it is...<br /> > <br />> <br /> > <br /> >>> suppose I have a field with the following values:<br /> > <br /> > <br /> ><br /> >>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2<br /> > <br /> > <br /> > <br /> >>> I needtwo seperate SELECT queries. One would return the<br /> >> following<br /> > <br /> >>> values (everythingleft of the decimal point)<br /> > <br /> > <br /> > <br /> >>> 77 77 134 134 5 5<br /> ><br /> > <br /> > <br /> >>> The second query would return all of the values to the right <br /> >>>of<br /> > <br /> >>> the decimal point:<br /> > <br /> > <br /> > <br /> >>> 12 1 2 3 1 2<br /> > <br /> > <br /> > <br /> > <br /> > <br /> >>> Now, I have been using the followinginformation (although <br /> >>> very<br /> > <br /> >>> Greek) to try to solve this problem:<br/> > <br /> > <br /> > <br /> > <br /> > <br /> > <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/functions-string.html">http://www.postgresql.org/docs/current/static/functions-string.html</a><br />><br /> ><br /> ><br /> ><br /> ><br /> ><br /> > </span><br /> And I have been playing around withthe syntax of the following:<br /><span style="white-space: pre;">> <br /> > <br /> > <br /> >>> substring('112.5'from '%#"___.#"_' for '#')<br /> > <br /> > <br /> > <br /> >>> but the aforementionedis not quite working out... can someone<br /> > <br /> >>> please show me a string function thatwill produce the desired<br /> > <br /> >>> results?<br /> > <br /> > <br /> > <br /> >>>Thanks! ~n<br /> > <br /> > <br /> > <br /> > </span><br /> -----BEGIN PGP SIGNATURE----- <br />Version: GnuPG v1.4.9 (MingW32) <br /> Comment: Using GnuPG with Mozilla - <a class="moz-txt-link-freetext" href="http://enigmail.mozdev.org/">http://enigmail.mozdev.org/</a><br/> <br /> iEYEARECAAYFAkuoOKoACgkQt6IL6XzynQSVFgCgvUGRoBgCwj2UDa3M9sfF6U3s<br /> Jm8AoMTL7Vb9ehj31y3Lv0PaNYV5tJhX <br /> =vITl <br/> -----END PGP SIGNATURE----- <br /><br />
-----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-----
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
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-----