Command guide
intermediate conversion MySQL MariaDB SQL Server PostgreSQL SQLite
Format A Number As A Decimal String
Convert a numeric value into a formatted string with a fixed number of decimal places, optionally including thousands separators.
5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green
Example 1
Format a price column with thousands separator and two decimal places
1234.5 stored as DECIMAL(10,2) is internally 1234.50. After formatting, 1234.50 becomes '1,234.50' with a thousands separator and two decimal places. 99.9 becomes '99.90' — the trailing zero is added to fill the requested decimal places. MySQL and MariaDB's FORMAT uses the server locale (default: en_US) which places a comma at thousands boundaries. SQL Server's 'N2' format specifier means numeric with 2 decimal places. PostgreSQL's FM9,999.00 mask specifies exactly four digits before the decimal (with comma grouping) and two after; the FM prefix removes leading whitespace that TO_CHAR would otherwise pad.
Source table data Setup
CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10, 2));
INSERT INTO
products (id, name, price)
VALUES
(1, 'Widget', 1234.5),
(2, 'Gadget', 99.9);
SQL
SELECT
id,
name,
FORMAT (price, 2) AS formatted_price
FROM
products
ORDER BY
id;
| id | name | formatted_price |
|---|
| 1 | Widget | 1,234.50 |
| 2 | Gadget | 99.90 |
Setup
CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10, 2));
INSERT INTO
products (id, name, price)
VALUES
(1, 'Widget', 1234.5),
(2, 'Gadget', 99.9);
SQL
SELECT
id,
name,
FORMAT (price, 'N2') AS formatted_price
FROM
products
ORDER BY
id;
| id | name | formatted_price |
|---|
| 1 | Widget | 1,234.50 |
| 2 | Gadget | 99.90 |
Setup
CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10, 2));
INSERT INTO
products (id, name, price)
VALUES
(1, 'Widget', 1234.5),
(2, 'Gadget', 99.9);
SQL
SELECT
id,
name,
TO_CHAR (price, 'FM9,999.00') AS formatted_price
FROM
products
ORDER BY
id;
| id | name | formatted_price |
|---|
| 1 | Widget | 1,234.50 |
| 2 | Gadget | 99.90 |
Setup
CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10, 2));
INSERT INTO
products (id, name, price)
VALUES
(1, 'Widget', 1234.5),
(2, 'Gadget', 99.9);
SQL
SELECT
id,
name,
printf ('%.2f', price) AS formatted_price
FROM
products
ORDER BY
id;
| id | name | formatted_price |
|---|
| 1 | Widget | 1234.50 |
| 2 | Gadget | 99.90 |
MySQL and MariaDB use FORMAT(n, 2); SQL Server uses FORMAT(n, 'N2'); PostgreSQL uses TO_CHAR(n, 'FM9,999.00'). The output strings are identical across all engines for this locale and input.