Home » RPAD Function in SQL

RPAD Function in SQL

by Online Tutorials Library

RPAD Function in SQL

This string function adds the symbol or the string to the right side of the original string. In Structured Query Language, we can use this function on both string and numbers.

Syntax of RPAD String Function

Syntax1: This syntax uses the RPAD function with the column name of the SQL table:

In this syntax, Column_Name is the name of column whose values are to be padded, size is the total length of the column value after padded, and rpad_string is that string which is to be added on the right side.

Syntax2: This syntax uses the RPAD function with the set of characters (string):

In this syntax, String is a value which is to be padded, size is the total length of column value after padded, and rpad_string is that string which is to be added on the right side of given original string.

Examples of RPAD String function

Example 1: Example 1: The following SELECT query adds the ‘#’ symbol three times to the right of the NEW string:

Output:

NEW###  

Example 2: The following SELECT query adds the ‘Site’ string to the right of the given original string:

Output:

TutorAspire Site  

Example 3: The following SELECT query adds the ‘9’ number four times to the right of given number 8:

Output:

89999  

Example 3: The following SELECT query adds the ‘yz’ string to the right of given character x:

Output:

xyz  

Example 5: This example uses the RPAD function with the SQL table

In this example, we are going to create a new table, which is used with the RPAD function.

The following CREATE statement creates the Faculty_Info table:

The below INSERT queries insert the records of college Faculties in the Faculty_Info table:

The following SELECT statement displays the inserted records of the above Faculty_Info table:

Faculty_Id Faculty_First_Name Faculty_Last_Name Faculty_Dept_Id Faculty_Address Faculty_City Faculty_Salary
1001 ARUSH SHARMA 4001 AMAN VIHAR DELHI 20000
1002 BULBUL ROY 4002 NIRMAN VIHAR DELHI 38000
1004 SAURABH ROY 4001 SECTOR 128 MUMBAI 45000
1005 SHIVANI SINGHANIA 4001 VIVEK VIHAR KOLKATA 42000
1006 AVINASH SHARMA 4002 SARVODYA CALONY DELHI 28000
1007 SHYAM BESAS 4003 KRISHNA NAGAR LUCKNOW 35000

The following SELECT query uses the RPAD function with the Faculty_Last_Name column of the above Faculty_Info table:

This SELECT statement adds the * symbol to the right of Last_Name of each faculty:

Output:

Faculty_Last_Name RPAD_LastName
SHARMA sharma*****
ROY roy********
ROY roy********
SINGHANIA singhania**
SHARMA sharma*****
BESAS besas******

The following SELECT query uses the RPAD function with the Faculty_First_Name, Faculty_City, and Faculty_Address columns of those faculties whose faculty_Id is greater than 1002 in the above Faculty_Info table:

Output:

Faculty_Id RPAD(Faculty_First_Name) RPAD(Faculty_Address) RPAD(Faculty_City)
1004 [email protected]@@ Sector 128####### mumbai$$$
1005 [email protected]@@ vivek vihar###### kolkata$$
1006 [email protected]@@ sarvodya calony## delhi$$$$
1007 @@@@@shyam krishna nagar#### lucknow$$

The following SELECT query uses the RPAD function with the Faculty_Salary column of the above Faculty_Info table:

This SELECT statement adds the 5 number to the right of salary of each faculty:

Output:

Faculty_Salary RPAD_Salary
20000 20000000
38000 38000000
45000 45000000
42000 42000000
28000 28000000
35000 35000000

You may also like