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.
-- PostgreSQL, MySQL, Oracle SELECT LPAD('42', 5, '0'); -- 00042
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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 trickSUBSTR('0000' || '123456', -4)keeps the last four (3456). Check the length first if values can be too long. printfpads 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 (
00042before00100), but you lose arithmetic. - Zero padding sorts text correctly. Without it,
'9'sorts after'10'as text. - Padding and
NULL.PRINTF('%05d', NULL)gives00000in SQLite, so test forNULLfirst if that matters. - Right-pad for fixed-width files, left-pad for numbers and ids.