SQL RPAD Function

Summary: in this tutorial, you’ll learn how to use the SQL RPAD function to pad a specified set of characters on the right of a string to a certain length.

Introduction to the SQL RPAD function #

RPAD stands for the right pad. The RPAD function allows you to pad a specified set of characters on the right of the string to a certain length.

Here’s the syntax of the RPAD function:

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

The RPAD function takes three parameters:

• string: The input string you want to pad.
• length: The length of the result string after padding.
• pad_string: The string used for padding. If you omit it, the RPAD function will use spaces by default.

The RPAD function returns a new string with the pad_string padded on the right of the input string. It ensures that the length of the result string is always equal to the input length.

Basic SQL RPAD function example #

The following query uses the RPAD function to pad the character (.) on the right of the string 'ABC' to a string with a length of 10:

SELECT RPAD('ABC', 10, '.') result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   result
------------
 ABC.......Code language: plaintext (plaintext)

The length of the string 'ABC' is three, so the RPAD function pads seven dots (.) to make the length of the result string 10.

The following example uses the RPAD function to pad the (.) to the right of the string 'ABC':

SELECT RPAD('ABC', 3, '.') result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
 ABCCode language: plaintext (plaintext)

Since the input string already has a length of 3, the result string will not have any padded characters.

Using the RPAD function with table data #

We’ll use the employees table from the sample database:

The following query uses the RPAD function to generate a report that includes the first name and employee IDs:

SELECT
  CONCAT(RPAD(first_name, 20, '.'), employee_id) report
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

         report
-------------------------
 Adam................121
 Alexander...........103
 Alexander...........115
 Britney.............193
 Bruce...............104
 Charles.............179
...Code language: plaintext (plaintext)

How it works:

• First, pad the character (.) to the right of the first name to make it 20 20-character length.
• Second, concatenate the result string with the employee id.

Summary #

• Use the SQL RPAD function to pad a string on the right with specified characters to a certain length.