How to Use LPAD and RPAD Functions in MySQL

In MySQL, use LPAD(string, length, pad_string) to add characters to the left of a string until it reaches the desired length, and RPAD(string, length, pad_string) to add characters to the right. Both functions return the padded string or truncate if the length is shorter than the original string.

Syntax

The LPAD and RPAD functions have the following syntax:

• LPAD(string, length, pad_string): Pads string on the left with pad_string until the total length is length.
• RPAD(string, length, pad_string): Pads string on the right with pad_string until the total length is length.

If the original string is longer than length, it will be truncated to that length.

sql

LPAD(string, length, pad_string)
RPAD(string, length, pad_string)

Example

This example shows how to use LPAD and RPAD to pad the string '42' to length 5 with zeros on the left and right.

sql

SELECT LPAD('42', 5, '0') AS left_padded, RPAD('42', 5, '0') AS right_padded;

Output

left_padded | right_padded
------------|-------------
00042       | 42000

Common Pitfalls

Common mistakes when using LPAD and RPAD include:

• Using a length smaller than the original string length, which causes truncation.
• Using an empty pad_string, which results in no padding.
• Assuming padding always adds characters without truncation.

Example of wrong and right usage:

sql

SELECT LPAD('hello', 3, '*') AS truncated,
       LPAD('hello', 8, '*') AS padded;

-- Output:
-- truncated: 'hel'
-- padded: '***hello'

Output

truncated | padded
----------|--------
hel       | ***hello

Quick Reference

Function	Purpose	Parameters	Behavior
LPAD(string, length, pad_string)	Pads string on the left	string: original text length: total length pad_string: text to pad	Pads or truncates string to length from left
RPAD(string, length, pad_string)	Pads string on the right	string: original text length: total length pad_string: text to pad	Pads or truncates string to length from right

Key Takeaways

Use LPAD to add characters to the left of a string up to a specified length.

Use RPAD to add characters to the right of a string up to a specified length.

If the specified length is shorter than the string, the result is truncated.

The pad_string can be any string, but empty pad_string results in no padding.

Always check the length parameter to avoid unexpected truncation.