SQL LPAD()

In SQL, LPAD()is a commonly used function that pads the left part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.

DBMSs that have an LPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.

DBMSs that don’t have an LPAD() function include SQL Server and SQLite (although there are other ways to apply left padding in these DBMSs).

Example

Here’s an example to demonstrate how to use LPAD() in your SQL code:

SELECT LPAD('Look Left', 20);

Result:

+-----------------------+
| LPAD('Look Left', 20) |
+-----------------------+
|            Look Left  |
+-----------------------+

In this example, the left part of the string is padded with a space (the default padding character), and the resulting string is 20 characters long (because I specified 20 as the second argument).

Oracle works the same, but we need to use FROM DUAL when doing a query like this (without querying an actual table):

SELECT LPAD('Look Left', 20) 
FROM DUAL;

Result:

    LPAD('LOOKLEFT',20) 
_______________________ 
           Look Left

Specify a Padding Character

The padding doesn’t necessarily need to be a space. We can optionally add a third argument to specify the character (or characters) to use in the padding.

SELECT LPAD('7', 3, '0');

Result:

In this case I padded a number with zeros. Actually, I passed the number as a string in this example.

In some DBMSs (such as MariaDB and MySQL) we can pass the number as a number, as well as the number to pad it with:

SELECT LPAD(7, 3, 0);

Result:

We can also do this in Oracle:

SELECT LPAD(7, 3, 0) 
FROM DUAL;

Result:

But PostgreSQL has a problem with this:

SELECT LPAD(7, 3, 0);

Result:

ERROR:  function lpad(integer, integer, integer) does not exist

SQL Server

SQL Server doesn’t have an LPAD() function, but it does have a FORMAT() function that allows us to pad numbers with leading zeros:

SELECT FORMAT(7, '000');

Result:

The way it works is that we pass the number, followed by a format string. In the above example, the format string is 000. This is a custom numeric format string that results in the original number being padded so that the result consists of three digits. If there aren’t already three digits in the original number, then it’s padded with zeros.

To left pad strings in SQL Server, we can do something like this:

SELECT RIGHT('.......' + 'Cat', 7);

Result:

....Cat

There are also a couple of other LPAD() equivalents in SQL Server.