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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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 <petr...@venaver.info> To: Neil Stlyz <neilst...@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-----
