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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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 <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 > > > >> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
