How the LPAD() Function Works in MySQL

In MySQL, the LPAD() function allows you to pad the left part of a string with one or more characters.

The way it works is, you specify the string to pad, the length of the padding, as well as the string of characters to use for the padding.

Syntax

The syntax goes like this:

LPAD(str,len,padstr)

Where str is the string to pad, len is the desired length of the string in characters after all padding has been applied, and padstr is the string to pad it with.

Example 1 – Basic Usage

Here’s an example of padding the left part of a string with the asterisk character:

SELECT LPAD('Cat', 6, '*') AS Result;

Result:

+--------+
| Result |
+--------+
| ***Cat |
+--------+

In this example the second argument is 6, which means that the whole string must end up being 6 characters in length after all padding has been applied. The third argument specifies which character to use for the padding.

So in this case, if we only want one asterisk, we can do this:

SELECT LPAD('Cat', 4, '*') AS Result;

Result:

+--------+
| Result |
+--------+
| *Cat   |
+--------+

Example 2 – Multiple Characters

You aren’t limited to just a single character. You can pad a string with any number of characters.

For example, we could take the previous example and add a space after the asterisk:

SELECT LPAD('Cat', 5, '* ') AS Result;

Result:

+--------+
| Result |
+--------+
| * Cat  |
+--------+

Note that we also increased the character count to 5 in order to accommodate the extra character.

Here’s another example using different characters:

SELECT LPAD('!', 15, 'Blah ') AS Result;

Result:

+-----------------+
| Result          |
+-----------------+
| Blah Blah Blah! |
+-----------------+

Example 3 – Second Argument is Too Small

If the value of the second argument is too small, you might end up with no padding:

SELECT LPAD('Cat', 3, '*') AS Result;

Result:

+--------+
| Result |
+--------+
| Cat    |
+--------+

In other cases, you could end up with the padding string being cut short, or could even cut the original string short:

SELECT 
  LPAD('Cat', 6, 'Puddy '),
  LPAD('Cat', 2, 'Puddy ');

Result:

+--------------------------+--------------------------+
| LPAD('Cat', 6, 'Puddy ') | LPAD('Cat', 2, 'Puddy ') |
+--------------------------+--------------------------+
| PudCat                   | Ca                       |
+--------------------------+--------------------------+

Note that these examples are for demonstration purposes only. In most cases, you wouldn’t use LPAD() to simply join two words together. To do that, you’d be better off using CONCAT() instead.

Example 4 – A Database Example

Here’s an example of selecting data from a database, and padding it on its left side:

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           |
+---------+----------------------+