Re: string functions and operators
От | Little, Douglas |
---|---|
Тема | Re: string functions and operators |
Дата | |
Msg-id | 8585BA53443004458E0BAA6134C5A7FB12C2B0@EGEXCMB01.oww.root.lcl обсуждение исходный текст |
Ответ на | Re: string functions and operators (Neil Stlyz <neilstylz@yahoo.com>) |
Список | 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-----
В списке pgsql-sql по дате отправления: