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: The
RPAD()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:
21becomes21years.
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 dataINSERT INTO StudentVALUES (02,'Paul','19','M','Lagos');INSERT INTO StudentVALUES (03,'Ameera','23','F','Imo');INSERT INTO StudentVALUES (04,'Maria','20','F','Lagos');INSERT INTO StudentVALUES (05,'David','25','M','Abuja');INSERT INTO StudentVALUES (06,'Niniola','26','F','Lagos');INSERT INTO StudentVALUES (08,'Joe','21', 'M','Lagos');-- QuerySELECT name, RPAD(Age,7, 'years' ) AS new_Age, genderFROM StudentOrder BY id;
Explanation
In the code above, we see the following:
- Lines 1–7: We create a table names
Student, which has the columnsid,name,Age,gender, andstate. - Lines 11–21: We insert data into the
Studenttable. - Lines 24–28: Using the
RPAD()function, we append the substringyearsto the stringAgecolumn. Finally, we sort the result by theidnumber.
TRENDING TOPICS
