Remove (.) from decimal values in postgres

Question

I want to remove . from all decimal value from my query output like if i use

select(32.00) output should come 3200
select(32.50) output should come 3250
select(32.05) output should come 3205

Is there is any function present to give me this output i dont want 3200.00 as output if anyone know plz tell me how ?

You really should specify what data type your 32.05 is. Is it money, decimal, real, double, ...? — mu is too short
– mu is too short, Commented Oct 7, 2011 at 6:29

mu is too short · Accepted Answer · 2011-10-07 16:31:17Z

10

You could multiply by 100 and cast to an integer:

=> select cast(32.00*100 as integer);
 int4 
------
 3200

That will give you an integer. If your values aren't the decimal(n,2) that they appear to be, then you might want to pull out round, floor, or ceil to make your desired rounding or truncation explicit:

=> select floor(23.025 * 100), ceil(23.025 * 100), round(23.025 * 100);
 floor | ceil | round 
-------+------+-------
  2302 | 2303 |  2303

edited Oct 7, 2011 at 16:31

answered Oct 7, 2011 at 6:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

ertx Over a year ago

One would think this would not work if the value was 32.001 but apparently it does

mu is too short Over a year ago

@ertx: The cast-to-int will work but it will truncate. I added a couple more options in case the incoming data isn't the decimal(n,2) that it appears to be.

Marco · Accepted Answer · 2011-10-07 06:06:56Z

1

You could try:

SELECT replace(CAST(your_field AS text),'.','')
FROM your_table

edited Oct 7, 2011 at 6:06

answered Oct 7, 2011 at 6:01

Marco

57.7k15 gold badges135 silver badges159 bronze badges

1 Comment

Marco Over a year ago

@mu is too short: is this better? Thanks :)

Collectives™ on Stack Overflow

Remove (.) from decimal values in postgres

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related