STRING PADDING (LPAD / RPAD)Functions
Buy Me A Coffee

Padding a string to a specified length by prepending (LPAD) or appending (RPAD) a fill character.

Pads a string to a specified length by prepending (LPAD) or appending (RPAD) a fill character. Useful for zero-padding numbers and formatting fixed-width output.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 LPAD and RPAD are both built-in. If the string is longer than the target length, the result is silently truncated to that length.
PostgreSQL ✓ Supported 7.4 LPAD and RPAD are built-in. The fill string defaults to a space if omitted. If the input is longer than length, the result is truncated from the right.
SQL Server ✗ Not Supported — No native LPAD or RPAD. Standard workarounds use RIGHT/LEFT with REPLICATE.
Oracle ✓ Supported 7 LPAD and RPAD are standard Oracle string functions, available in SQL and PL/SQL.
SQLite ✗ Not Supported — No native LPAD or RPAD. Use printf() / format() for left-zero-padding integers. General string padding requires application-side logic or a custom UDF.

Details

LPAD and RPAD are available in MySQL, PostgreSQL, and Oracle but absent in SQL Server and SQLite. SQL Server's REPLICATE-based pattern is verbose but reliable and worth encapsulating in a scalar function if used frequently. SQLite's printf('%0Nd', value) handles numeric zero-padding cleanly but there is no general-purpose string-padding path without a custom UDF. A subtle shared behavior in MySQL and PostgreSQL: if the source string is already longer than the target length, it is silently truncated to that length — data loss that can go unnoticed if the target length is set too small.

Standard Syntax

SELECT LPAD('42', 6, '0'); -- '000042' SELECT RPAD('hi', 5, '.'); -- 'hi...'

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Not supported Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

LPAD and RPAD are both built-in. If the string is longer than the target length, the result is silently truncated to that length.

SELECT LPAD('42', 6, '0'); -- '000042' SELECT RPAD('hello', 8, '-'); -- 'hello---'

PostgreSQL

LPAD and RPAD are built-in. The fill string defaults to a space if omitted. If the input is longer than length, the result is truncated from the right.

SELECT LPAD('42', 6, '0'); -- '000042' SELECT RPAD('hello', 8, '-'); -- 'hello---'

SQL Server

No native LPAD or RPAD. Standard workarounds use RIGHT/LEFT with REPLICATE.

-- LPAD equivalent: SELECT RIGHT(REPLICATE('0', 6) + '42', 6); -- '000042' -- RPAD equivalent: SELECT LEFT('hello' + REPLICATE('-', 8), 8); -- 'hello---'

Oracle

LPAD and RPAD are standard Oracle string functions, available in SQL and PL/SQL.

SELECT LPAD('42', 6, '0') FROM dual; -- '000042' SELECT RPAD('hello', 8, '-') FROM dual; -- 'hello---'

SQLite

No native LPAD or RPAD. Use printf() / format() for left-zero-padding integers. General string padding requires application-side logic or a custom UDF.

-- Zero-pad integer: SELECT printf('%06d', 42); -- '000042'