
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How to format number to 2 decimal places in MySQL?
You can use TRUNCATE() function from MySQL to format number to 2 decimal places. The syntax is as follows −
SELECT TRUNCATE(yourColumnName,2) as anyVariableName from yourTableName;
To understand the above syntax, let us first create a table. The query to create a table is as follows −
mysql> create table FormatNumberTwoDecimalPlace -> ( -> Number float -> ); Query OK, 0 rows affected (0.59 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into FormatNumberTwoDecimalPlace values(123.456); Query OK, 1 row affected (0.13 sec) mysql> insert into FormatNumberTwoDecimalPlace values(1.6789); Query OK, 1 row affected (0.19 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12.2); Query OK, 1 row affected (0.14 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12356.23145); Query OK, 1 row affected (0.40 sec) mysql> insert into FormatNumberTwoDecimalPlace values(12356); Query OK, 1 row affected (0.14 sec) mysql> insert into FormatNumberTwoDecimalPlace values(.5678); Query OK, 1 row affected (0.28 sec)
Let us now display all records from the table using select command. The query is as follows −
mysql> select *from FormatNumberTwoDecimalPlace;
Output
+---------+ | Number | +---------+ | 123.456 | | 1.6789 | | 12.2 | | 12356.2 | | 12356 | | 0.5678 | +---------+ 6 rows in set (0.04 sec)
Here is the query to format number to two decimal places −
mysql> select truncate(Number,2) as TwoValueAfterDecimal from FormatNumberTwoDecimalPlace;
Output
+----------------------+ | TwoValueAfterDecimal | +----------------------+ | 123.45 | | 1.67 | | 12.19 | | 12356.23 | | 12356.00 | | 0.56 | +----------------------+ 6 rows in set (0.00 sec)
- Related Articles
- How to get number located at 2 places before decimal point MySQL?
- Format amount values for thousands number with two decimal places in MySQL?
- Java Program to format to 2 decimal places in a 10-character field
- How to use String Format to show decimal up to 2 places or simple integer in C#?
- How do I format a number as decimal to store it in MySQL?
- How to format all decimal places in an R vector and data frame?
- How to round a number to n decimal places in Java
- Select rows having more than 2 decimal places in MySQL?
- I want to round a number to 2 decimal places in SAPUI5. Could anyone help?
- Display 3 decimal places in MySQL?
- Parse and format a number to decimal in Java
- Java Program to Round a Number to n Decimal Places
- Swift Program to Round a Number to n Decimal Places
- Haskell Program to Round a Number to n Decimal Places
- Kotlin Program to Round a Number to n Decimal Places

Advertisements