Formatting Output

FORMAT, LPAD and RPAD in MySQL 8.4, what they do to sorting and locale, and why formatting usually belongs in the application.

SyntaxFORMAT(X, D [, locale]) | LPAD(str, len, padstr) | RPAD(str, len, padstr)

MySQL 8.4 has no printf or sprintf function. What it offers is a small set of purpose-built formatters, and a good reason to use them sparingly.

The functions

FORMAT(X, D [, locale]) renders a number with thousands separators, rounded to D decimal places, and returns a string: FORMAT(12332.123456, 4) gives '12,332.1235'. With D of 0 there is no decimal part. The optional third argument selects the decimal point, thousands separator and grouping; the default is 'en_US', so FORMAT(12332.2, 2, 'de_DE') gives '12.332,20'.

LPAD(str, len, padstr) and RPAD(str, len, padstr) pad a string to exactly len characters. Both are multibyte safe, and both return NULL if any argument is NULL.

Why keep it out of SQL

Every one of these returns a string. Sort on a FORMAT() result and '1,000' orders before '900'; compare it and you are comparing text. The locale is then fixed by whoever wrote the query rather than by the user's settings, and the application receives a string it has to parse back before it can do arithmetic. Return the raw DECIMAL or DATETIME and let the presentation layer format it. Reach for these functions for fixed-width exports, generated reference codes, and output read straight off a terminal.

Example

Example · sql
-- A fixed-width export that a legacy accounting system ingests.
-- This is the case where formatting genuinely belongs in SQL:
-- the output is a file, not data the application will compute with.
SELECT CONCAT(
  -- Reference: 'INV-' plus the id zero-padded to 8 characters
  RPAD(CONCAT('INV-', LPAD(i.id, 8, '0')), 16, ' '),
  -- Name clipped and padded to exactly 30 columns
  RPAD(SUBSTRING(c.name, 1, 30), 30, ' '),
  -- Amount right-aligned in 14 columns, 2 decimals. FORMAT() always inserts
  -- thousands separators, so strip them for a plain numeric field.
  LPAD(REPLACE(FORMAT(i.amount, 2), ',', ''), 14, ' '),
  -- Dates as YYYYMMDD, which is what the receiving system expects
  DATE_FORMAT(i.issued_on, '%Y%m%d')
) AS record_line
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.issued_on >= '2026-09-01'
-- Sort on the underlying column, never on the formatted string
ORDER BY i.issued_on, i.id;

When to use it

  • Produce a fixed-width or padded text file for a system that cannot consume JSON or CSV
  • Build a zero-padded human-facing reference such as ORD-000481 from an auto-increment id
  • Format currency totals for a report read directly from the mysql client
  • Right-align numeric columns in ad hoc terminal output while debugging

More examples

What FORMAT does to sorting

FORMAT returns a string, and string ordering is lexical. Keep the raw column in the SELECT list and order by that. If you catch yourself comparing or summing a FORMAT result, the formatting has moved one step too early in the pipeline.

Example · sql
-- Wrong: the ORDER BY sorts text, so descending puts '900.00' above '1,000.00'
SELECT FORMAT(amount, 2) AS amount
FROM payments
ORDER BY FORMAT(amount, 2) DESC;

-- Right: sort the number, display the string
SELECT amount            AS amount_raw,   -- DECIMAL, for the application
       FORMAT(amount, 2) AS amount_shown  -- VARCHAR, for the eye only
FROM payments
ORDER BY amount DESC;

Zero-padded reference codes

LPAD is the usual way to build a human-facing reference from an auto-increment id. The truncation line is the trap: once ids pass six digits the padded reference starts losing its leading digits, so size len for the largest id you expect. If the reference is searched on, store it in an ordinary column with a UNIQUE index so a collision becomes an error rather than a silent duplicate — a generated column will not do, because MySQL forbids an AUTO_INCREMENT column as a base column in a generated column definition.

Example · sql
SELECT
  id,
  CONCAT('ORD-', LPAD(id, 6, '0'))    AS reference,   -- 'ORD-000481'
  LPAD('12345', 3, '0')               AS truncated,   -- '123', silently
  RPAD(country_code, 3, ' ')          AS fixed_width
FROM orders;

-- If the reference is queried, store it rather than recomputing it.
-- A generated column will NOT work here: MySQL does not allow an
-- AUTO_INCREMENT column as a base column in a generated column definition.
ALTER TABLE orders
  ADD COLUMN reference VARCHAR(10) NULL,
  ADD UNIQUE INDEX (reference);

-- Backfill the existing rows; new rows get the value from the application
UPDATE orders
SET reference = CONCAT('ORD-', LPAD(id, 6, '0'))
WHERE reference IS NULL;

Locale is a decision, not a default

FORMAT defaults to 'en_US' when no locale is given, which means a query written in one office formats numbers wrongly for users in another. The application knows the user's locale and the database does not, which is the strongest argument for returning the raw number and formatting at the edge.

Example · sql
SELECT
  FORMAT(12332.2, 2)            AS en_us,   -- '12,332.20'
  FORMAT(12332.2, 2, 'de_DE')   AS de_de,   -- '12.332,20'
  FORMAT(12332.2, 0)            AS rounded; -- '12,332'

Related

sql string functionssql numeric functionssql castsql date functionssql generated columns

Discussion

  • Be the first to comment on this lesson.