string functions and operators

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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/

iEYEARECAAYFAkuoHlIACgkQt6IL6XzynQTJ/ACfX4mSteAz9CmZLnPCayz+jXQI
IhoAnA7qrFHNmRVObfSvE+YXZ0OKr3MS
=wvB9
-----END PGP SIGNATURE-----

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

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

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/

iEYEARECAAYFAkuoOKoACgkQt6IL6XzynQSVFgCgvUGRoBgCwj2UDa3M9sfF6U3s
Jm8AoMTL7Vb9ehj31y3Lv0PaNYV5tJhX
=vITl
-----END PGP SIGNATURE-----

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

silly me says..

SELECT FLOOR(x), x-FLOOR(x) FROM TABLE;

at least for numeric values.

but, in string space, ummmm...

select split_part(x,'.',1), split_part(x,'.',2) from table;

or
select regexp_replace(x, '\.[0-9]*$',''),
regexp_replace(x,'^[0-9]*\.','') from table;

or god knows how many others.

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

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

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-owner@postgresql.org [mailto:pgsql-sql-owner@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 <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

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

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