LPAD()
SQL · String Functions
Theory
LPAD(string, length, pad) adds the pad character(s) to the left of a string until it reaches the target length. LPAD('7', 3, '0') returns '007' and LPAD('42', 5, '0') returns '00042'. If the string is already the target length, it comes back unchanged. If it is longer, LPAD cuts it down to length, keeping the left part, so LPAD('123456', 3, '0') gives '123', which can silently damage data.
LPAD is available in MySQL, Postgres, Oracle and Snowflake, but not in SQL Server or SQLite. In SQL Server the usual trick is RIGHT('00000' + CAST(id AS VARCHAR), 5). In SQLite you can write SUBSTR('00000' || id, -5) or use printf('%05d', id). These tricks keep the right part when the value is too long, unlike LPAD. The classic use is fixed-width IDs: invoice numbers like INV-00042, employee codes and file names that should sort correctly as text.
Why we use it
Numbers stored or shown as text sort badly ('10' comes before '9') and look inconsistent. Zero-padding to a fixed width fixes sorting and gives the neat codes that invoices, reports and file names need.
When to use it
Use LPAD for zero-padded codes and right-aligned fixed-width text. Make sure the length is at least as long as your biggest value, or data gets cut. If you only need formatting for display, a number format function may be simpler.
How it works
- Take the string, target length and pad character
- Is the string longer than the target? Yes: Cut it to the target length. No: Continue.
- Count how many characters are missing
- Repeat the pad on the left to fill the gap
- Return the fixed-width string
Syntax
LPAD(string, length, pad_char)Example
SELECT LPAD('7', 3, '0');
-- '007'Key points
- Common for zero-padding IDs or invoice numbers
- Not available in SQLite or SQL Server: use SUBSTR/RIGHT tricks
- LPAD truncates values longer than the target length
- The SUBSTR emulation keeps the right part when too long, LPAD the left
- Padded text sorts correctly: '00009' comes before '00010'