MySQL LPAD() and RPAD() – Add Arbitrary Characters to a String

By /
LPAD And RPAD Functions

In this tutorial, we will learn about MySQL LPAD() and MySQL RPAD() functions in MySQL. Padding is the process of adding some unnecessary material in your strings, often to meet a minimum word count. Padding in MySQL can be very useful in formatting the output of a query. MySQL provides us with two padding functions – LPAD() and RPAD().

  • MySQL LPAD() is used to left-pad (add padding on the left side) a string with another string, to a specified length.
  • MySQL RPAD() is used to right-pad (add padding on the right side) a string with another string, to a specified length. 

Syntax of MySQL LPAD()

LPAD(string, length, padding_string);Code language: SQL (Structured Query Language) (sql)

Where

  • ‘string’ is the original string that needs left padding,
  • ‘length’ is the length that the string should have after padding and,
  • ‘padding_string’ is the string that should be left-padded to ‘string’.

Syntax of MySQL RPAD()

RPAD(string, length, padding_string);Code language: SQL (Structured Query Language) (sql)

Where

  • ‘string’ is the original string that needs right padding,
  • ‘length’ is the length that the string should have after padding and,
  • ‘padding_string’ is the string that should be right-padded to ‘string’.

Examples of MySQL LPAD()

Let us look at a basic example of MySQL LPAD(). Consider the below query. We use an alias with our SELECT statement to make our output readable.

SELECT LPAD('MySQL', 10, '*') AS Result;Code language: SQL (Structured Query Language) (sql)

In the above query, we want to left-pad ‘MySQL’ with ‘*’ and the resulting padded string should be 10 characters long. We get the output as follows,

MySQL Lpad Basic Example

Multi-character Left Padding Using MySQL LPAD()

LPAD() lets you specify multiple characters in the ‘padding_string’ parameter. Let us see this using the below example.

SELECT LPAD('MySQL', 10, '*#') AS Result;Code language: SQL (Structured Query Language) (sql)

And you get the output as follows,

MySQL Lpad Multi Character Padding

MySQL LPAD() When ‘length’ Is Too Small

Suppose your original string is 10 characters long but you want your padded string to be of length 7. What happens then? In such a case, the ‘length’ parameter is too small – smaller than the original string itself. Let us see how LPAD() reacts to it using the below example.

SELECT LPAD('Earth', 5, 'The ') AS Result; 
SELECT LPAD('Earth', 3, 'The ') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Lpad Length Too Small

The first query says you want your left-padded string to be of length 5. But your original string is already 5 characters in length so LPAD() doesn’t pad anything. In the second query, the value of the ‘length’ parameter is even smaller than the original string. In this case, LPAD() cuts short the string and returns only the first 3 characters from the left of the string.

LPAD() With Tables

Consider the below ConferenceGuests table.

Conference Guests Table Find In Set
ConferenceGuests Table

Let us left-pad every state in the above table with ‘.’ such that the resulting padded string is 11 characters long. We use the below query.

SELECT State, LPAD(State, 11, '.') AS Result FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Lpad Table Example 1

Note that, we can also pad a string with whitespaces. Let us left-pad the Country column with single whitespace such that the resulting string is 13 characters long. The query for it is,

SELECT Country, LPAD(Country, 13, ' ') AS Result FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And the output is,

Lpad Table Example 2

Examples of MySQL RPAD()

Let us look at a basic example of MySQL RPAD(). Consider the below query. We use an alias with our SELECT statement to make our output readable.

SELECT RPAD('Hello', 7, '!') AS Result;Code language: SQL (Structured Query Language) (sql)

In the above query, we want to right-pad ‘Hello’ with ‘!’ and the resulting padded string should be 7 characters long. We get the output as follows,

Rpad Basic Example

Multi-character Right Padding Using MySQL RPAD()

MySQL RPAD() lets you specify multiple characters in the ‘padding_string’ parameter. Let us see this using the below example.

SELECT RPAD('Hello', 12, ' World!') AS Result;Code language: SQL (Structured Query Language) (sql)

And you get the output as follows,

Rpad Multi Character Padding

MySQL RPAD() When ‘length’ Is Too Small

MySQL RPAD() behaves similar to LPAD() when the ‘length’ parameter is equal to or lower than the length of the original string. We can see this using the below example.

SELECT RPAD('Hello', 4, ' World!') AS Result; 
SELECT RPAD('Hello', 5, ' World!') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

Rpad Length Too Small

MySQL RPAD() With Tables

We will consider the same ConferenceGuests table we did for LPAD(). Let us right-pad every state in the ConferenceGuests table with ‘.’ such that the resulting padded string is 11 characters long. We use the below query.

SELECT State, RPAD(State, 11, '.') AS Result FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Rpad Table Example

Conclusion

LPAD() and RPAD() are mainly used for the formatting of the output of a query. I would encourage you to play around with them to practice them.