using Replace funcion in postgresql

On 9/16/2013 4:55 PM, karinos57 wrote:

SELECT
Volume, REPLACE(Volume,'.','')
FROM MyTable

The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks

still getting _the_ error? what error is that? can we assume your
table field is a numeric rather than a string value?

you could use to_char(volume*100, 'FM9999999'), or maybe even just
(volume*100)::integer

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

just as FYI the database i am using is Netezza so my data type is CHARACTER
VARYING(6). The error i am getting is 'Buffer Overflow'.
So the funny thing is when i change this '' to this ' ' then the query runs
but it is putting a blank space between the values like this 88 97
but i get an error when i only make with out space like this ''. thanks

--
View this message in context: http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164p5771171.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/16/2013 04:55 PM, karinos57 wrote:

SELECT
Volume, REPLACE(Volume,'.','')
FROM MyTable

The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks

SELECT
Volume, REPLACE(Volume::text,'.','')::int
FROM MyTable

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

karinos57 wrote

SELECT
Volume, REPLACE(Volume,'.','')
FROM MyTable

The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks

Solution provided elsewhere but make sure you cover the corner-case where
there are zeros in the decimal positions. Depending on the how the number
column is defined those may be lost whereas I presume you want "00" to
appear at the end for integer amounts and "90" to appear to round-tenths.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164p5771184.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/16/2013 06:20 PM, karinos57 wrote:

just as FYI the database i am using is Netezza so my data type is CHARACTER
VARYING(6). The error i am getting is 'Buffer Overflow'.
So the funny thing is when i change this '' to this ' ' then the query runs
but it is putting a blank space between the values like this 88 97
but i get an error when i only make with out space like this ''. thanks

Alright now I am confused. Your subject says using replace in Postgres,
yet now you say you are using Netezza, which is it?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general