RPAD()

Syntax

How it works

RPAD() takes a string, a target length and a fill text, then adds copies of the fill on the right hand side until the value is exactly length characters wide. It is the mirror image of LPAD(), which pads on the left instead. Left padding is normal for numbers you want right aligned; right padding is normal for labels and text you want left aligned in a fixed width column.

The fill text does not have to be a single character. If you pass a longer fill such as RPAD(name, 12, ". ") the pattern repeats and is cut off at the target length, so you can build dotted leaders and simple rulers. When the input string is already at least as long as length, RPAD does not grow it; it returns the first length characters, which is a form of truncation worth remembering.

RPAD is most useful when you generate plain text reports, fixed width export files or aligned console output, where every column has to start at the same character position. For pulling a slice back out of such fixed width text you would reach for SUBSTRING(). See the full SQL functions reference for related string tools.

Examples

Pad a label to a fixed width

-- pad each dish name out to 10 characters with dots
SELECT RPAD(name, 10, '.') AS menu_line
FROM dishes;

menu_line
----------
Falafel...
Hummus....
Shawarma..

Build a fixed width export line with RPAD

-- every column starts at the same position
SELECT CONCAT(
         RPAD(sku,  8, ' '),
         RPAD(name, 12, ' '),
         price
       ) AS export_line
FROM products;

export_line
-------------------------
A100    Olive Oil   45.00
B220    Tahini      12.50
C307    Za'atar      9.75

Default fill is a space (PostgreSQL two argument form)

-- fill argument omitted, so PostgreSQL pads with spaces
SELECT '[' || RPAD(code, 6) || ']' AS boxed
FROM tags;

boxed
--------
[AB   ]
[LONG  ]
[X     ]

SQL Server emulation (no RPAD function)

-- SQL Server has no RPAD, so append spaces then trim to length
SELECT LEFT(name + REPLICATE(' ', 10), 10) AS padded
FROM products;

padded
----------
Olive Oil 
Tahini    
Za'atar   

Align a numbered list

SELECT RPAD(name, 14, ' ') || qty AS stock_line
FROM inventory
ORDER BY name;

stock_line
----------------
Bulgur        40
Lentils       12
Rice          85

Common mistakes

-- name is longer than 5, so it is CUT DOWN, not padded
SELECT RPAD('Watermelon', 5, '.') AS r;
-- r = 'Water'

-- pick a length at least as wide as the longest value
SELECT RPAD('Watermelon', 12, '.') AS r;
-- r = 'Watermelon..'

-- SQL Server does not have an RPAD function
SELECT RPAD(name, 10, ' ') FROM products;
-- error: RPAD is not a recognized built-in function name

-- emulate it with LEFT plus REPLICATE
SELECT LEFT(name + REPLICATE(' ', 10), 10) AS padded
FROM products;

-- MySQL requires the fill argument; two args is an error
SELECT RPAD(name, 10) FROM products;

-- always pass the fill text in MySQL
SELECT RPAD(name, 10, ' ') FROM products;

Performance

RPAD() is a lightweight scalar function that runs once per row, so on its own it adds very little cost. The expense shows up when you pad millions of rows in an export or wrap the padded value in an ORDER BY, because the engine has to build a new string for every row before it can sort.

Never filter on a padded column, for example WHERE RPAD(code, 8, '0') = 'AB000000'. Wrapping a column in a function makes the predicate non sargable, so the optimiser cannot use an index on code and falls back to a full scan. Compare against the raw column and pad the literal side instead.

For very large fixed width exports it is often cheaper to select the raw columns and let the application layer do the padding, since streaming plain values avoids building and discarding large temporary strings inside the database.

Interview questions

SELECT RPAD('Watermelon', 5, '.') AS r;  -- 'Water'