using Replace function in postgresql

Question

I am trying not to show the period in the values using the Replace function but I am getting Buffer Overflow error when I use my select statement like this:

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. How can I achieve this?

you need to cast volume to a string prior to replacing the decimal point with an empty string. Can't give you the prosgresql syntax for that though. — John Bingham
– John Bingham, Commented Sep 16, 2013 at 23:42
the data type is varchar in this case (character varying in postgres) — moe
– moe, Commented Sep 16, 2013 at 23:45

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-09-17 00:08:44Z

1

One way: historical PostgreSQL syntax.

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

Another way: standard SQL syntax.

SELECT Volume, REPLACE(cast(Volume as text),'.','')
FROM MyTable

Yet another way: PostgreSQL function-like syntax.

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

edited Sep 17, 2013 at 0:08

answered Sep 17, 2013 at 0:03

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

moe Over a year ago

thanks i really appreciate your help. i have tried but i am getting this error "on NPS CLOB data type" I am using Netezza database which uses PostgreSQL and the type for this field is Character Varying(6).

Mike Sherrill 'Cat Recall' Over a year ago

PostgreSQL doesn't have a CLOB data type. But Oracle does.

Collectives™ on Stack Overflow

using Replace function in postgresql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related