How to remove zeros after decimal in PostgreSQL?

by raphael_tillman , in category: PHP Databases , 2 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 , a year 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.