Syntax error Remove trailing zeros in decimal value with changing length in MySQL?

Remove trailing zeros in decimal value with changing length in MySQL?

You can remove trailing zeros using TRIM() function. The syntax is as follows.

SELECT TRIM(yourColumnName)+0 FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table removeTrailingZeroInDecimal
   -> (
   -> Id int not null auto_increment,
   -> Amount decimal(5,2),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.01 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50);
Query OK, 1 row affected (0.22 sec)

mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05);
Query OK, 1 row affected (0.17 sec)

mysql> insert into removeTrailingZeroInDecimal(Amount) values(12.050);
Query OK, 1 row affected (0.14 sec)

mysql> insert into removeTrailingZeroInDecimal(Amount) values(125.23);
Query OK, 1 row affected (0.14 sec)

mysql> insert into removeTrailingZeroInDecimal(Amount) values(125.00);
Query OK, 1 row affected (0.15 sec)

mysql> insert into removeTrailingZeroInDecimal(Amount) values(126);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from removeTrailingZeroInDecimal;

The following is the output.

+----+--------+
| Id | Amount |
+----+--------+
|  1 | 405.50 |
|  2 | 23.05  |
|  3 | 12.05  |
|  4 | 125.23 |
|  5 | 125.00 |
|  6 | 126.00 |
+----+--------+
6 rows in set (0.00 sec)

Here is the query to remove trailing zeros in decimal value. The query is as follows −

mysql> SELECT TRIM(Amount)+0 FROM removeTrailingZeroInDecimal;

The output displays the records without the trailing zeros.

+----------------+
| TRIM(Amount)+0 |
+----------------+
|          405.5 |
|          23.05 |
|          12.05 |
|         125.23 |
|            125 |
|            126 |
+----------------+
6 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

11K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements