Padding in SQL

Some RDBMSs provide an LPAD() and RPAD() function that enables us to left pad or right pad a string. Some functions also allow us to add leading or trailing zeros numbers.

Below are examples of applying SQL padding in some of the more popular RDBMSs.

Oracle

Oracle provides us with LPAD() and RPAD() specifically for padding either the left or right part of a string:

SELECT LPAD('Cat', 20)
FROM DUAL;

Result:

         LPAD('CAT',20) 
_______________________ 
                 Cat

In this case I used LPAD() to apply left padding, and the resulting string is 20 characters, because that’s what I used for the second argument.

The result is padded by a space, because that’s the default character used for padding. If you don’t specify which character to pad the string with, a space is used.

You can add a third character to specify which character to use for the padding:

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

Result:

   LPAD('7',3,'0') 
__________________ 
007

In this case I padded a number with zeros (although, the result is a string).

Oracle also has a TO_CHAR(number) function that can be used to add leading zeros to a number:

SELECT TO_CHAR(7, 'fm000')
FROM DUAL;

Result:

The 0 format element represents each digit in the original number, and zero if no digit exists in that position in the original number.

PostgreSQL

PostgreSQL also has its own LPAD() and RPAD() functions:

SELECT 
    LPAD('7', 3, '0') AS "Left Padding",
    RPAD('7', 3, '0') AS "Right Padding",
    LPAD(RPAD('7', 3, '0'), 5, '0') AS "Both";

Result:

 Left Padding | Right Padding | Both  
--------------+---------------+-------
 007          | 700           | 00700

And it also has its own TO_CHAR() function that works much like Oracle’s function of the same name:

SELECT TO_CHAR(7, 'fm000');

Result:

MariaDB

MariaDB has its own LPAD() and RPAD() functions:

SELECT 
    LPAD('7', 3, '0') AS "Left Padding",
    RPAD('7', 3, '0') AS "Right Padding",
    LPAD(RPAD('7', 3, '0'), 5, '0') AS "Both";

Result:

+--------------+---------------+-------+
| Left Padding | Right Padding | Both  |
+--------------+---------------+-------+
| 007          | 700           | 00700 |
+--------------+---------------+-------+

And while MariaDB does have its own TO_CHAR() function, it doesn’t work on numbers (it’s limited to datetime values).

MySQL

MySQL also has its own LPAD() and RPAD() functions.

Here’s an example of using LPAD() against a database column:

SELECT Genre, LPAD(Genre, 10, '.')
FROM Genres;

Result:

+---------+----------------------+
| Genre   | LPAD(Genre, 10, '.') |
+---------+----------------------+
| Rock    | ......Rock           |
| Jazz    | ......Jazz           |
| Country | ...Country           |
| Pop     | .......Pop           |
| Blues   | .....Blues           |
| Hip Hop | ...Hip Hop           |
| Rap     | .......Rap           |
| Punk    | ......Punk           |
+---------+----------------------+

SQL Server

SQL Server is a bit of an anomaly in that it doesn’t have an LPAD() or RPAD() function.

However, SQL Server does have a FORMAT() function that enables us to easily pad numbers with leading zeros:

SELECT FORMAT(7, '000');

Result:

The second argument is a custom numeric format string that determines how many characters the resulting string will have. The fact that I used zeros means that the result will have leading zeros if needed.

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

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

Result:

....Cat

There are various other techniques we can use for applying padding in SQL Server.