Re: string functions and operators

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)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(at)venaver(dot)info>
To: Neil Stlyz <neilstylz(at)yahoo(dot)com>; pgsql-sql mailing list <pgsql-sql(at)postgresql(dot)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-----