LPAD()

Syntax

How it works

LPAD() stands for left pad. It takes a value, a target length, and a fill string, then repeats the fill on the left side of the value until the result is exactly the target length. Its mirror image is RPAD(), which fills on the right instead.

The most common use is turning a plain number into a fixed width code. An invoice id of 42 becomes 000042 when you pad it to six characters with zeros, so every id sorts and lines up neatly. LPAD is also used to align text columns in reports and to build fixed layout strings such as account numbers or reference codes.

Because LPAD() works on text, numeric inputs are normally passed through a string conversion such as CAST first. Keep in mind that padding is a display concern: it is usually cheaper and cleaner to store the raw number and pad only when you present it, rather than storing the padded text.

One important gotcha: SQL Server has no LPAD() function. You emulate it with RIGHT(REPLICATE(pad, n) + value, n), which prepends enough copies of the pad and then keeps the rightmost n characters.

Examples

Zero pad an invoice id to six digits

SELECT LPAD(CAST(id AS CHAR), 6, '0') AS invoice_no
FROM invoices;

invoice_no
==========
000042
000107
013500

Pad with spaces to align a column

SELECT LPAD(name, 10, ' ') AS aligned
FROM products;

aligned
==========
     Apple
    Banana
      Mango

Build a fixed width badge code

SELECT LPAD(dept_code, 8, '*') AS badge
FROM staff;

badge
========
***SALES
*FINANCE
******IT

Emulate LPAD in SQL Server (no native LPAD)

/* SQL Server has no LPAD, so prepend zeros then keep the last 6 chars */
SELECT RIGHT(REPLICATE('0', 6) + CAST(id AS VARCHAR(6)), 6) AS invoice_no
FROM invoices;

invoice_no
==========
000042
000107
013500

Common mistakes

/* The number is already 9 digits, longer than 6.
   LPAD does NOT leave it alone, it TRUNCATES it. */
SELECT LPAD('123456789', 6, '0');
/* returns 123456, the account number is now wrong */

/* Pick a length that fits your widest value, or
   validate the input length before padding */
SELECT LPAD('123456789', 9, '0');
/* returns 123456789 */

/* Runs on MySQL, PostgreSQL and SQLite,
   but FAILS on SQL Server: no such function */
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM invoices;

/* Portable SQL Server version */
SELECT RIGHT(REPLICATE('0', 6) + CAST(id AS VARCHAR(6)), 6)
FROM invoices;

/* Passing a raw number can behave oddly or error,
   because LPAD expects text, not an integer */
SELECT LPAD(id, 6, '0') FROM invoices;

/* Cast the number to text first */
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM invoices;

Performance

LPAD() itself is very cheap: it builds one short string per row. The real cost comes from where you apply it. Padding is a formatting step, so run it in the final SELECT that feeds your report, not deep inside a subquery that processes millions of intermediate rows.

Never pad a column inside a WHERE or JOIN predicate on a large table, for example WHERE LPAD(code, 8, '0') = '00001234'. Wrapping the column in a function hides it from any index, forcing a full scan. Pad the constant on the other side of the comparison instead, or store a canonical value.

When you only need padded output for humans, consider padding in the application layer after the rows come back. That keeps the database doing set work and moves cheap string formatting to the client. For more text helpers see the full SQL functions reference and the SQL functions list.

Interview questions

SELECT LPAD(CAST(id AS CHAR), 6, '0') AS invoice_no
FROM invoices;

SELECT RIGHT(REPLICATE('0', 6) + CAST(id AS VARCHAR(6)), 6) AS invoice_no
FROM invoices;