TRIM(): Oracle PL/SQL TRIM function examples and usages

PL/SQL String Functions: TRIM()

Exercise 1:

Write a PL/SQL block that prompts the user to enter a string and removes any leading and trailing zeros from it. Display the trimmed string.

Sample Solution:

PL/SQL Code:

DECLARE
v_input_stringVARCHAR2(100);
v_trimmed_stringVARCHAR2(100);
BEGIN
v_input_string := '00000test string00000';

v_trimmed_string := TRIM('0' FROM v_input_string);
  DBMS_OUTPUT.PUT_LINE('Trimmed String: [' || v_trimmed_string || ']');
END;

Sample Output:

Trimmed String: [test string]

Explanation:

The said code in Oracle's PL/SQL that takes an input string, removes leading and trailing zeros from it.

The variables v_input_string of type VARCHAR2(100) and v_trimmed_string of type VARCHAR2(100) are declares that holds the input string and the trimmed string, respectively.

The input string is assigned the value '00000test string00000'.

The TRIM function removes leading and trailing zeros from v_input_string and the trimmed string is assigned to the v_trimmed_string variable.

The DBMS_OUTPUT.PUT_LINE procedure displays the trimmed string.

Flowchart:

Exercise 2:

Write a PL/SQL block that retrieves the first name and last name of employees in the employees table and removes any leading and trailing spaces from them. Display the trimmed names.

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;
BEGIN
  FOR emp IN (SELECT first_name, last_name FROM employees) LOOP
v_first_name := TRIM(BOTH ' ' FROM emp.first_name);
v_last_name := TRIM(BOTH ' ' FROM emp.last_name);
    DBMS_OUTPUT.PUT_LINE('First Name: [' || v_first_name || '], Last Name: [' || v_last_name || ']');
  END LOOP;
END;

Sample Output:

First Name: [Kelly], Last Name: [Chung]
First Name: [Jennifer], Last Name: [Dilly]
First Name: [Timothy], Last Name: [Gates]
First Name: [Randall], Last Name: [Perkins]
First Name: [Sarah], Last Name: [Bell]
First Name: [Britney], Last Name: [Everett]
First Name: [Samuel], Last Name: [McCain]
First Name: [Vance], Last Name: [Jones]
First Name: [Alana], Last Name: [Walsh]
First Name: [Kevin], Last Name: [Feeney]
First Name: [Donald], Last Name: [OConnell]
First Name: [Douglas], Last Name: [Grant]
First Name: [Jennifer], Last Name: [Whalen]

......

Explanation:

The said code in Oracle's PL/SQL that iterates through each row in the employees table, trims leading and trailing spaces from the first name and last name values, and then outputs the trimmed values.

The variables v_first_name and v_last_name, both are of the same data type as the corresponding columns in the employees table are declared that holds the trimmed first name and last name, respectively.

The FOR loop that fetches the first name and last name values for each rows from the employees table.

The TRIM function removes leading and trailing spaces from the first name and last name values retrieved from each row. The BOTH keyword that trimmed from both ends of the string.

The trimmed first name is assigned to the v_first_name variable, and the trimmed last name is assigned to the v_last_name variable.

The DBMS_OUTPUT.PUT_LINE displays the trimmed first name and last name values that are enclosed within square brackets for clarity.

Flowchart:

Exercise 3:

Write a PL/SQL block that retrieves the department names from the departments table and removes any leading and trailing underscores from them. Display the trimmed department names.

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;
BEGIN
  FOR dept IN (SELECT department_id, department_name FROM departments) LOOP
v_department_id := dept.department_id;
v_department_name := TRIM(BOTH '_' FROM dept.department_name);
    DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_department_id || ', Trimmed Department Name: [' || v_department_name || ']');
  END LOOP;
END;

Sample Output:

Department ID: 10, Trimmed Department Name: [Administration]
Department ID: 20, Trimmed Department Name: [Marketing]
Department ID: 30, Trimmed Department Name: [Purchasing]
Department ID: 40, Trimmed Department Name: [Human Resources]
Department ID: 50, Trimmed Department Name: [Shipping]
Department ID: 60, Trimmed Department Name: [IT]
Department ID: 70, Trimmed Department Name: [Public Relation]
Department ID: 80, Trimmed Department Name: [Sales]
Department ID: 90, Trimmed Department Name: [Executive]
Department ID: 100, Trimmed Department Name: [Finance]
Department ID: 110, Trimmed Department Name: [Accounting]
Department ID: 120, Trimmed Department Name: [Treasury]
Department ID: 130, Trimmed Department Name: [Corporate Tax]

......

Explanation:

The said code in Oracle's PL/SQL that retrieves data from the departments table and trims leading and trailing underscores from the department name.

The variables v_department_id and v_department_name which are the data types of the department_id and department_name columns in the departments table, respectively that holds the department ID and the trimmed department name.

The FOR loop fetches the department ID and department name values for each row from the departments table.

The department ID is assigned to the v_department_id variable without any modification and the department name is assigned to the v_department_name variable after removes all the leading and trailing underscores from the department name.

The DBMS_OUTPUT.PUT_LINE displays the department ID and the trimmed department name enclosed within square brackets.

Flowchart:

Exercise 4:

Write a PL/SQL block that prompts the user to enter a string and removes any leading or trailing spaces from it. Display the trimmed string.

PL/SQL Code:

DECLARE
v_input_stringVARCHAR2(100);
v_trimmed_stringVARCHAR2(100);
BEGIN
v_input_string := '     trim_5_leading_and_trailing_spaces     ';
v_trimmed_string := TRIM(v_input_string);
  DBMS_OUTPUT.PUT_LINE('Trimmed String: [' || v_trimmed_string || ']');
END;

Sample Output:

Trimmed String: [trim_5_leading_and_trailing_spaces]

Flowchart:

Improve this sample solution and post your code through Disqus

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