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 any Language for FREE all September 🎉
For the entire month of September, get unlimited access to our entire catalog of beginner coding resources.
🎁 G i v e a w a y
30 Days of Code
Complete Educative’s daily coding challenge every day in September, and win exciting Prizes.

Free Resources

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