FORMAT and LPAD/RPAD in MySQL

Introduction

FORMAT helps show numbers nicely with commas and decimals. LPAD and RPAD add characters to the left or right of text to make it a certain length.

When you want to show money amounts with commas and two decimals.

When you need to make a text field a fixed length by adding spaces or zeros on the left.

When you want to add characters on the right side of a string to align text.

When preparing data for reports that need neat columns.

When formatting numbers for easier reading by people.

Syntax

MySQL

FORMAT(number, decimal_places)
LPAD(string, length, pad_string)
RPAD(string, length, pad_string)

FORMAT adds commas and rounds the number to the given decimal places.

LPAD and RPAD add the pad_string repeatedly until the string reaches the desired length.

Examples

Shows the number with commas and 2 decimals: '1,234,567.89'

MySQL

SELECT FORMAT(1234567.891, 2);

Adds zeros on the left to make the string length 3: '007'

MySQL

SELECT LPAD('7', 3, '0');

Adds '*' on the right to make length 5: 'Hi***'

MySQL

SELECT RPAD('Hi', 5, '*');

Sample Program

This query formats a number with 1 decimal, pads '42' on the left with zeros to length 5, and pads 'Cat' on the right with '!' to length 6.

MySQL

SELECT FORMAT(9876543.21, 1) AS formatted_number,
       LPAD('42', 5, '0') AS left_padded,
       RPAD('Cat', 6, '!') AS right_padded;

Important Notes

If the string is already longer than the length in LPAD or RPAD, it returns the original string.

FORMAT returns a string, not a number, so you cannot do math on the result without converting it back.

Pad strings can be more than one character, and they repeat as needed.

Summary

FORMAT makes numbers easier to read by adding commas and decimals.

LPAD adds characters to the left of a string to reach a set length.

RPAD adds characters to the right of a string to reach a set length.