Sometimes you need to pad a string with spaces. Or perhaps you’ll pad it with another character. Sometimes you need to pad it on the left. Other times you need to pad it on the right. Or maybe you need to pad it on both sides.
All of the above can be done in MySQL using the LPAD() and/or RPAD() string functions.
Syntax
The syntax of each of these functions goes like this:
LPAD(str,len,padstr) RPAD(str,len,padstr)
Where str is the string that needs padding, 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 – LPAD()
Here’s an example of padding the left side of a string:
SELECT LPAD('Cat', 6, '*') AS Result;
Result:
+--------+ | Result | +--------+ | ***Cat | +--------+
In this example we pad the string with the asterisk character (*). We also specify 6, which means that the whole string must end up being 6 characters in length after all padding has been applied.
So if we change the 6 to say, 10, here’s what happens:
SELECT LPAD('Cat', 10, '*') AS Result;
Result:
+------------+ | Result | +------------+ | *******Cat | +------------+
Example – RPAD()
The RPAD() function works exactly the same as LPAD(), with the exception that it adds the characters to the right side of the string:
SELECT RPAD('Cat', 6, '*') AS Result;
Result:
+--------+ | Result | +--------+ | Cat*** | +--------+
Example – Both
You can use both functions together to pad a string on both sides. To do this, simply pass one function to the other as an argument.
Like this:
SELECT LPAD(RPAD('Cat', 6, '*'), 9, '*') AS Result;
Result:
+-----------+ | Result | +-----------+ | ***Cat*** | +-----------+
Multiple Characters
You aren’t limited to just a single character. You can pad a string with any number of characters. Here’s an example:
SELECT RPAD('She Loves Me ', 30, 'Yeah! ') AS Result;
Result:
+--------------------------------+ | Result | +--------------------------------+ | She Loves Me Yeah! Yeah! Yeah! | +--------------------------------+