sqlcmd.net validated sql reference

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.

Created Last updated 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.

MySQL MariaDB
Engine-specific syntax
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);
Validated query
SQL
SELECT
  id,
  name,
  FORMAT (price, 2) AS formatted_price
FROM
  products
ORDER BY
  id;
Expected result
idnameformatted_price
1Widget1,234.50
2Gadget99.90
SQL Server
Engine-specific syntax
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);
Validated query
SQL
SELECT
  id,
  name,
  FORMAT (price, 'N2') AS formatted_price
FROM
  products
ORDER BY
  id;
Expected result
idnameformatted_price
1Widget1,234.50
2Gadget99.90
PostgreSQL
Engine-specific syntax
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);
Validated query
SQL
SELECT
  id,
  name,
  TO_CHAR (price, 'FM9,999.00') AS formatted_price
FROM
  products
ORDER BY
  id;
Expected result
idnameformatted_price
1Widget1,234.50
2Gadget99.90
SQLite
Engine-specific syntax
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);
Validated query
SQL
SELECT
  id,
  name,
  printf ('%.2f', price) AS formatted_price
FROM
  products
ORDER BY
  id;
Expected result
idnameformatted_price
1Widget1234.50
2Gadget99.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.

Useful when

Where this command helps.

  • formatting currency or measurement values for display in reports without relying on application-layer formatting
  • rounding and padding decimal output to a consistent precision for comparison or export

Explanation

What the command is doing.

Number formatting converts a raw numeric value into a human-readable string with controlled precision and locale-appropriate separators. MySQL and MariaDB use FORMAT(number, decimal_places) which returns a locale-formatted string with thousands separators (e.g., '1,234.50'). SQL Server uses FORMAT(number, format_string) where 'N2' means two decimal places with thousands grouping. PostgreSQL uses TO_CHAR(number, format_mask) where 'FM9,999.00' produces the same style; the FM prefix suppresses leading spaces. All four engines produce '1,234.50' for 1234.5 with two decimal places.

Tags

numberformatdecimalstringconversion

Related commands

Keep moving through nearby tasks.

Official documentation