LPAD

Pad the input string on the left with a repeating pad sequence until it reaches a target length.

Category: stringReturns: VARCHARDialect: Standard

Syntax

LPAD(str, len [, pad])

Description

## Overview Returns the input string padded on the left to the specified total character length by repeating the pad sequence. If the input is already at least as long as the target length, it is truncated from the right to fit. LPAD is most commonly used to zero-pad numeric strings for stable lexicographic sorting and to align fields in fixed-width exports. When the pad argument is multi-character, LPAD concatenates as many copies as needed and then truncates the last partial copy so that the result has exactly the requested length. LPAD(str, len) without a pad argument defaults to space padding. ## Behavior - Returns NULL when any argument is NULL. - If `len` is less than or equal to 0, returns an empty string. - If `len` is less than the character length of `str`, the input is truncated from the right to exactly `len` characters. - If `pad` is an empty string, the input is returned unchanged (no padding can be applied). - Operates on Unicode code points; multi-byte characters in both `str` and `pad` count as one position. - The pad sequence is repeated starting at the left of the gap; the last repetition is truncated if it would overshoot. ## Compatibility - Widely supported with matching semantics. - Treats the target length as a character count, not a byte count.

Parameters

Name	Type	Description
`str`		Specifies the input string to pad.
`len`		Specifies the desired total character length of the result. If the input is longer than this, it is truncated from the right.
`pad`		Specifies the pad string. If multi-character, it is repeated and then trimmed to fit the required gap. Defaults to a single space.

Examples

-- Pad with spaces
SELECT LPAD('hi', 5);  -- '   hi'

-- Pad with zeros to a fixed width
SELECT LPAD('42', 5, '0');  -- '00042'

-- Truncate when target length is shorter than input
SELECT LPAD('hello', 3, ' ');  -- 'hel'

-- Multi-character pad sequence is repeated
SELECT LPAD('x', 6, 'ab');  -- 'ababax'

-- No padding needed when lengths match
SELECT LPAD('hello', 5, ' ');  -- 'hello'

-- NULL propagates
SELECT LPAD(CAST(NULL AS VARCHAR), 5, '0');  -- NULL

Pitfalls

LPAD truncates the input when the target length is smaller. This silent truncation is a common source of data corruption when the length is computed dynamically.
The length is a character count, not a byte count. Zero-padding a string of CJK or emoji characters produces a result that may exceed an anticipated byte budget.
LPAD with an empty pad string cannot fill the gap and returns the input unchanged. Always validate the pad argument.
When zero-padding signed numbers, a leading minus sign stays at the left edge and pushes padding between the sign and the digits. Convert the number to a sign-separated form or use a FORMAT-based approach for signed values.