RPAD() : Oracle PL/SQL function Examples and Usage

Last update on November 22 2023 11:19:40 (UTC/GMT +8 hours)

PL/SQL String Functions: RPAD()

Exercise 1:

Write a PL/SQL block that retrieves the first name and repeats the last name three times for all employees in the employees table. Display the results.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
v_repeated_last_nameVARCHAR2(150);
BEGIN
  FOR emp IN (SELECT first_name, last_name FROM employees) LOOP
v_first_name := emp.first_name;
v_last_name := emp.last_name;
v_repeated_last_name := RPAD(v_last_name, LENGTH(v_last_name)*3, v_last_name);
    DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name || ', Repeated Last Name: ' || v_repeated_last_name);
  END LOOP;
END
/

Sample Output:

First Name: Steven     , Repeated Last Name: King        KingKing
First Name: Neena      , Repeated Last Name: KochharKochharKochhar
First Name: Lex        , Repeated Last Name: De Haan     De Haan     De Haan
First Name: Alexander  , Repeated Last Name: HunoldHunoldHunold
First Name: Bruce      , Repeated Last Name: Ernst       ErnstErnst
First Name: David      , Repeated Last Name: Austin      AustinAustin
First Name: Valli      , Repeated Last Name: PataballaPataballaPataballa
First Name: Diana      , Repeated Last Name: Lorentz     LorentzLorentz
First Name: Nancy      , Repeated Last Name: Greenberg   GreenbergGreenberg
First Name: Daniel     , Repeated Last Name: FavietFavietFaviet
First Name: John       , Repeated Last Name: Chen        ChenChen
First Name: Ismael     , Repeated Last Name: SciarraSciarraSciarra
First Name: Jose Manue , Repeated Last Name: UrmanUrmanUrman

.......

Explanation:

The said code in Oracle's PL/SQL that returns the first name along with the three times repeated last name from the 'employees' table.

The variables v_first_name, v_last_name, and v_repeated_last_name are the same data type as the corresponding columns in the 'employees' table is declared.

The FOR loop iterates through each row in the 'employees' table and fetches the first name and last name of the employees and assigned to the variables v_first_name and v_last_name, respectively.

The RPAD() function takes three arguments the string to be repeated, the total length of the resulting string which repeats the last name three times, and the character to use for padding. The result is assigned to the v_repeated_last_name variable.

The DBMS_OUTPUT.PUT_LINE statement displays the first name and the repeated last name for each employee.

Flowchart:

Exercise 2:

Write a PL/SQL block that prompts the user to enter a string and a repetition count. Use the RPAD function to repeat the string by the specified count. Display the repeated string.

PL/SQL Code:

DECLARE
v_input_stringVARCHAR2(100);
v_repetition_count NUMBER;
v_repeated_stringVARCHAR2(1000);
BEGIN
v_input_string := ' AAA ';
v_repetition_count := 3;
v_repeated_string := RPAD(v_input_string, LENGTH(v_input_string)*v_repetition_count, v_input_string);
  DBMS_OUTPUT.PUT_LINE('Repeated String: ' || v_repeated_string);
END;
/

Sample Output:

Repeated String:  AAA  AAAAAA

Explanation:

The said code in Oracle's PL/SQL that returns the repetition of string ' AAA ' three times.

The three variables, v_input_string of type VARCHAR2(100), v_repetition_count of type NUMBER, and v_repeated_string of type VARCHAR2(1000) are declared.

The code then assigns a value to v_input_string, which is ' AAA ' . It also assigns a value of 3 to v_repetition_count, indicating the number of times the input string should be repeated.

The RPAD function takes the arguments the string to be padded, the total length of the resulting string, and the padding character.

The DBMS_OUTPUT.PUT_LINE procedure displays the "Repeated String: " with the value of v_repeated_string and prints it to the console.

Flowchart:

Exercise 3:

Write a PL/SQL block that finds the department names and repeats them twice for all departments in the departments table. Display the results.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
v_dept_iddepartments.department_id%TYPE;
v_dept_namedepartments.department_name%TYPE;
v_repeated_dept_nameVARCHAR2(150);
BEGIN
  FOR dept IN (SELECT department_id, department_name FROM departments) LOOP
v_dept_id := dept.department_id;
v_dept_name := dept.department_name;
v_repeated_dept_name := RPAD(v_dept_name, LENGTH(v_dept_name)*2, v_dept_name);
    DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_dept_id || ', Repeated Department Name: ' || v_repeated_dept_name);
  END LOOP;
END;

Sample Output:

Department ID: 10, Repeated Department Name: AdministrationAdministration
Department ID: 20, Repeated Department Name: MarketingMarketing
Department ID: 30, Repeated Department Name: PurchasingPurchasing
Department ID: 40, Repeated Department Name: Human ResourcesHuman Resources
Department ID: 50, Repeated Department Name: ShippingShipping
Department ID: 60, Repeated Department Name: ITIT
Department ID: 70, Repeated Department Name: Public RelationPublic Relation
Department ID: 80, Repeated Department Name: SalesSales
Department ID: 90, Repeated Department Name: ExecutiveExecutive
Department ID: 100, Repeated Department Name: FinanceFinance
Department ID: 110, Repeated Department Name: AccountingAccounting
......

Explanation:

The said code in Oracle's PL/SQL that retrieves department information from the 'departments' table and prints the department ID along with a repeated version of the department name.

The variables v_dept_id and v_dept_name are the same data type of the "department_id" and "department_name" column from the 'departments' table and the another variable, v_repeated_dept_name, is declared as a VARCHAR2(150).

The "FOR" loop iterates over the rows returned by the query which retrieves the department ID and department name from the 'departments' table.

Inside the loop, the department ID and department name are assigned to the corresponding variables v_dept_id and v_dept_name.

The RPAD() pads the department name with additional copies of itself, up to twice its original length.

The DBMS_OUTPUT.PUT_LINE statement displays the department ID and the repeated department name.

Flowchart:

Exercise 4:

Write a PL/SQL block that prompts the user to enter a number and generates a string consisting of that many space characters. Display the generated string.

PL/SQL Code:

DECLARE
v_space_count NUMBER;
v_spacesVARCHAR2(100);
BEGIN
v_space_count := 5 ;
v_spaces := RPAD(' ', v_space_count);
  DBMS_OUTPUT.PUT_LINE('Generated Spaces: [' || v_spaces || ']');
END;

Sample Output:

Generated Spaces: [     ]

Explanation:

The said code in Oracle's PL/SQL that prompts the user to enter a number (in this case, 5), generates a string consisting of the specified number of spaces, and then outputs the generated spaces.

The variables v_space_count of type NUMBER which holds the number of spaces to generate and v_spaces of type VARCHAR2(100) which stores the generated spaces.

The variable v_space_count assigns by the value 5. This determines the number of spaces to generate.

The RPAD() generates a string of spaces. The first parameter to RPAD is the character to repeat, the second parameter is the length of the resulting string.

The DBMS_OUTPUT.PUT_LINE function displays the output message, which includes the value of v_spaces enclosed in square brackets.

Flowchart:

Exercise 5:

Write a PL/SQL block that generates a string consisting of 10 space characters and concatenates it with the job title of employees in the employees table. Display the concatenated string.

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_job_idemployees.job_id%TYPE;
v_spacesVARCHAR2(10) := RPAD(' ', 10);
v_concatenated_stringVARCHAR2(100);
BEGIN
  FOR emp IN (SELECT job_id FROM employees) LOOP
v_job_id := emp.job_id;
v_concatenated_string := v_spaces || v_job_id;
    DBMS_OUTPUT.PUT_LINE('Concatenated String: [' || v_concatenated_string || ']');
  END LOOP;
END;

Sample Output:

Concatenated String: [          AD_PRES]
Concatenated String: [          AD_VP]
Concatenated String: [          AD_VP]
Concatenated String: [          IT_PROG]
Concatenated String: [          IT_PROG]
Concatenated String: [          IT_PROG]
Concatenated String: [          IT_PROG]
Concatenated String: [          IT_PROG]
Concatenated String: [          FI_MGR]
Concatenated String: [          FI_ACCOUNT]
Concatenated String: [          FI_ACCOUNT]
Concatenated String: [          FI_ACCOUNT]
Concatenated String: [          FI_ACCOUNT]
........

Explanation:

The said code in Oracle's PL/SQL that iterates through the job IDs in the 'employees' table, adds ten spaces before each job ID.

The variable v_job_id of type employees.job_id column, v_spaces of type VARCHAR2 and initialized with ten spaces, and

v_concatenated_string of type VARCHAR2 with a length of 100 characters.

The FOR loop retrieves each row from the result obtaindedfrom a SELECT statement and assigns it to the loop variable emp.

Within the loop, the job_id value from the current row is assigned to the variable v_job_id.

The v_spaces variable is concatenated with v_job_id to create v_concatenated_string.

The DBMS_OUTPUT.PUT_LINE displays the concatenated string along with some additional formatting to the output.

The loop continues until all rows from the query result have been processed.

Flowchart:

Exercise 6:

Write a PL/SQL block that generates a string consisting of 5 space characters and uses it to left-pad the department name of departments in the departments table. Display the padded department name.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
v_department_iddepartments.department_id%TYPE;
v_department_namedepartments.department_name%TYPE;
v_spacesVARCHAR2(5) := RPAD(' ', 5);
v_padded_department_nameVARCHAR2(100);
BEGIN
  FOR dept IN (SELECT department_id, department_name FROM departments) LOOP
v_department_id := dept.department_id;
v_department_name := dept.department_name;
v_padded_department_name := v_spaces || v_department_name;
    DBMS_OUTPUT.PUT_LINE('Padded Department Name: [' || v_padded_department_name || ']');
  END LOOP;
END;

Sample Output:

Padded Department Name: [     Administration]
Padded Department Name: [     Marketing]
Padded Department Name: [     Purchasing]
Padded Department Name: [     Human Resources]
Padded Department Name: [     Shipping]
Padded Department Name: [     IT]
Padded Department Name: [     Public Relation]
Padded Department Name: [     Sales]
Padded Department Name: [     Executive]
Padded Department Name: [     Finance]
Padded Department Name: [     Accounting]
Padded Department Name: [     Treasury]
Padded Department Name: [     Corporate Tax]
Padded Department Name: [     Control AndCre]
Padded Department Name: [     Shareholder Ser]
Padded Department Name: [     Benefits]
Padded Department Name: [     Manufacturing]

........

Explanation:

The said code in Oracle's PL/SQL that retrieves the department IDs and department names from the 'departments' table and performs padding of five spaces before each department name.

The variable v_department_id of type departments.department_id, v_department_name of type departments.department_name, v_spaces of type VARCHAR2 and initialized with five spaces, v_padded_department_name of type VARCHAR2 with a length of 100 characters are declares.

The BEGIN keyword indicates the start of the executable portion of the PL/SQL block.

The FOR loop retrieves each row from the result obtained from a SELECT query and assigns it to the loop variable dept.

The v_spaces variable is concatenated with v_department_name to create v_padded_department_name.

The DBMS_OUTPUT.PUT_LINE procedure displays the padded department name.

The loop continues until all rows from the query result have been processed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: LOWER() Functions.
Next: REPLACE() Function.