Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL LPAD() and RPAD() functions

Reference for SQL LPAD() and RPAD(): pad text to a fixed width with a fill character, truncation rules, and equivalents for SQLite and SQL Server.

LPAD(s, width, fill) adds fill characters on the left of a string until it is width characters long. RPAD pads on the right. They produce fixed-width output: invoice numbers such as 00042, aligned columns in text reports, and sortable codes.

PostgreSQL, MySQL and Oracle have both functions. SQLite and SQL Server do not. In SQLite use printf() or string slicing; in SQL Server use RIGHT() with REPLICATE() or FORMAT(). The examples below run in SQLite.

SQL
-- PostgreSQL, MySQL, Oracle
SELECT LPAD('42', 5, '0');   -- 00042

Try it

SQL Zero-padded numbers in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQL Aligned report lines ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL A padding helper made of REPLACE and SUBSTR

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
LPAD(string, width [, fill])
RPAD(string, width [, fill])
Name Description
string The text to pad. Numbers must be converted to text in most databases.
width The length of the result.
fill Optional. The padding text (default: a space). It is repeated and cut to fit.

Returns: the padded string. If string is already longer than width, it is cut to width characters (the right side is dropped in both functions). NULL if any argument is NULL.

Dialect differences

Database Support
SQLite no LPAD/RPAD; use printf('%05d', n), printf('%-10s', s) or substr() slicing
PostgreSQL LPAD, RPAD
MySQL LPAD, RPAD (fill is required)
SQL Server none; RIGHT(REPLICATE('0', 5) + CAST(n AS varchar(5)), 5), or FORMAT(n, '00000')
Oracle LPAD, RPAD
SQL
-- SQL Server: zero-pad to five digits
SELECT RIGHT('00000' + CAST(42 AS varchar(5)), 5);
SELECT FORMAT(42, '00000');

Usage notes

  • Truncation differs by method. LPAD('123456', 4, '0') keeps the first four characters (1234), while the SQLite slicing trick SUBSTR('0000' || '123456', -4) keeps the last four (3456). Check the length first if values can be too long.
  • printf pads on width. '%05d' pads integers with zeros, '%5d' pads with spaces and '%-5d' left-aligns. For text use '%-10s' (left-aligned) or '%10s'.
  • Padding is for presentation. Store numbers as numbers and pad on output. A padded code stored as text sorts correctly (00042 before 00100), but you lose arithmetic.
  • Zero padding sorts text correctly. Without it, '9' sorts after '10' as text.
  • Padding and NULL. PRINTF('%05d', NULL) gives 00000 in SQLite, so test for NULL first if that matters.
  • Right-pad for fixed-width files, left-pad for numbers and ids.