Re: string functions and operators

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