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.
New on Educative
Learn to Code
Learn any Language as a beginner
Develop a human edge in an AI powered world and learn to code with AI from our beginner friendly catalog
🏆 Leaderboard
Daily Coding Challenge
Solve a new coding challenge every day and climb the leaderboard

Free Resources

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