How to remove zeros after decimal in PostgreSQL?

by raphael_tillman , in category: PHP Databases , 3 years ago

How to remove zeros after decimal in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

by hal.littel , 2 years ago

@raphael_tillman 

To remove zeros after the decimal point in a numeric value in PostgreSQL, you can use the trunc function. This function truncates a number to a specified number of decimal places.


For example, if you have a numeric column value with the value 10.5000 and you want to remove the zeros after the decimal point, you can use the following statement:

1
SELECT TRUNC(value, 2) AS truncated_value FROM mytable;


This will return 10.50 as the truncated_value.


You can also use the round function to achieve a similar result. The round function rounds a number to a specified number of decimal places. For example:

1
SELECT ROUND(value, 2) AS rounded_value FROM mytable;


This will return 10.50 as the rounded_value.


Note that both trunc and round will return a numeric value. If you want to cast the result to a string and remove the trailing zeros, you can use the to_char function. For example:

1
SELECT TO_CHAR(TRUNC(value, 2), '9999D99') AS truncated_value FROM mytable;


This will return 10.5 as the truncated_value. The format string '9999D99' specifies that the number should be rounded to two decimal places and that any trailing zeros should be removed.


I hope this helps! Let me know if you have any questions.

by jasen_gottlieb , 2 years ago

@raphael_tillman 

To remove the zeros after the decimal in PostgreSQL, you can use the TRUNC() function or the CAST() function.

  1. Using the TRUNC() function:
1
SELECT TRUNC(column_name) FROM table_name;


Replace column_name with the name of the column that contains the number you want to remove zeros from and table_name with the name of the table where the column resides.

  1. Using the CAST() function:
1
SELECT CAST(column_name AS integer) FROM table_name;


Replace column_name with the name of the column that contains the number you want to remove zeros from and table_name with the name of the table where the column resides.


Both approaches will remove the decimal and any trailing zeros, converting the number into an integer.

Related Threads:

How to remove leading zeros in PostgreSQL?
How to cast as decimal in postgresql?
How to remove PostgreSQL from linux ubuntu?
How to remove xml attributes in postgresql?
How to remove special characters from a string in postgresql?
How to add leading zeros in mongodb?