How to use the RPAD() function in SQL

Overview

The RPAD() function is used to append a substring to a string of a certain length.

Syntax

RPAD(string,length,fill_string)

Parameter

  • string: This represents the string to be filled.
  • length: This represents the length of the string after the substring has been filled.
  • fill_string: This represents the substring that will fill the string to the specified length.

Note: TheRPAD() function adds the substring to the right-hand side of the parent string.

Example

Let’s assume that you were given a task to append the string years to the Age column in the Student table.

Output: 21 becomes 21years.

The following code demonstrates how to use the RPAD() function to solve this problem in SQL.

CREATE TABLE Student (
    id int,
    name varchar(50),
    Age varchar(50),
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Student
VALUES (02,'Paul','19','M','Lagos');
INSERT INTO Student
VALUES (03,'Ameera','23','F','Imo');
INSERT INTO Student
VALUES (04,'Maria','20','F','Lagos');
INSERT INTO Student
VALUES (05,'David','25','M','Abuja');
INSERT INTO Student
VALUES (06,'Niniola','26','F','Lagos');
INSERT INTO Student
VALUES (08,'Joe','21', 'M','Lagos');
-- Query 
SELECT name, RPAD(Age,7, 'years' ) AS new_Age, gender
FROM Student
Order BY id;

Explanation

In the code above, we see the following:

  • Lines 1–7: We create a table names Student, which has the columns id, name, Age, gender, and state.
  • Lines 11–21: We insert data into the Student table.
  • Lines 24–28: Using the RPAD() function, we append the substring years to the string Age column. Finally, we sort the result by the id number.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)