
- 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 with “.” as thousand separators, and “,” as decimal separator?
You can use format() function for the separators. It will work in MySQL version greater than or equal to 5.5. We are using the version 8.0.12
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)
The syntax is as follows
SELECT FORMAT(yourColumnName, valueAfterDecimalPoint, 'de_DE') AS anyAliasNamefrom yourTableName;
To understand the above syntax, let us create a table. The query to create a table is as follows
mysql> create table formatNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Number DECIMAL(19,1) -> ); Query OK, 0 rows affected (1.13 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into formatNumberDemo(Number) values(10000000000000.2); Query OK, 1 row affected (0.15 sec) mysql> insert into formatNumberDemo(Number) values(1000.4); Query OK, 1 row affected (0.21 sec) mysql> insert into formatNumberDemo(Number) values(1000000.6); Query OK, 1 row affected (0.11 sec) mysql> insert into formatNumberDemo(Number) values(100000000.7); Query OK, 1 row affected (0.17 sec) mysql> insert into formatNumberDemo(Number) values(100000000000000000.8); Query OK, 1 row affected (0.15 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from formatNumberDemo;
The following is the output
+----+----------------------+ | Id | Number | +----+----------------------+ | 1 | 10000000000000.2 | | 2 | 1000.4 | | 3 | 1000000.6 | | 4 | 100000000.7 | | 5 | 100000000000000000.8 | +----+----------------------+ 5 rows in set (0.00 sec)
Here is the query to format number with '.' as thousand separator, and ',' as decimal separator
mysql> SELECT FORMAT(Number, 3, 'de_DE') AS Numberformat from formatNumberDemo;
The following is the output
+-----------------------------+ | Numberformat | +-----------------------------+ | 10.000.000.000.000,200 | | 1.000,400 | | 1.000.000,600 | | 100.000.000,700 | | 100.000.000.000.000.000,800 | +-----------------------------+ 5 rows in set (0.00 sec)
- Related Articles
- How to set comma as decimal separator in R?
- Program to find number with thousand separator in Python
- Print number with commas as 1000 separators in Python
- Print number with commas as 1000 separators in C#
- How to print a number with commas as thousands of separators in JavaScript?
- How do I format a number as decimal to store it in MySQL?
- How to place a thousand separator in MySQL records?
- How to format Java LocalDateTime as ISO_DATE_TIME format
- How to get string as date in MySQL with dates as dot format specifier?
- Parse and format a number to decimal in Java
- Write \( \frac{7}{8} \) as a decimal number.
- How to format number to 2 decimal places in MySQL?
- Java Program to format LocalDateTime as ISO_WEEK_DATE format
- Java Program to format LocalTimeDate as BASIC_ISO_DATE format
- How to format and display date in Java as '201904'

Advertisements