Thread: using Replace funcion in postgresql
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
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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
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.
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.
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