PLSQL | TRIM Function

The PLSQL TRIM function is used for removing all specified characters either from the beginning or the end of a string. The TRIM function accepts three parameters among which the first parameter can either have one of the values 'LEADING', 'TRAILING', 'Both' and Trim_character and input_string.

• If LEADING is specified, then Oracle Database removes any leading characters equal to trim_character.
• If TRAILING is specified, then Oracle removes any trailing characters equal to trim_character.
• If BOTH or none of the three is specified, then Oracle removes leading and trailing characters equal to trim_character.
• If trim_character is not specified, then the default value is a blank space.
• If only input_string is specified, then Oracle removes leading and trailing blank spaces.
• If either trim_source or trim_character is null, then the TRIM function returns null.

Syntax:

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] input_string )

Parameters Used:

1. LEADING: This parameter is used to remove the trim_character from the front of input_string.
2. TRAILING: This parameter is used to remove trim_character from the end of input_string.
3. BOTH: This parameter is used to remove trim_character from the front and end of input_string.
4. input_string: It is used to specify the source string.
5. trim_character: It is used to specify the string that will be removed from the input_string.

Return Value: The TRIM function in PLSQL returns a string value. Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1: Using the LEADING parameter to remove the trim_character from the front of input_string.

DECLARE 
   Test_String string(25) := '1Geeksforgeeks1';
   
BEGIN 
   dbms_output.put_line(TRIM(LEADING '1' FROM Test_String)); 
   
END;      

Output:

Geeksforgeeks1 

Example-2: Using the TRAILING parameter to remove the trim_character from the end of input_string.

DECLARE 
   Test_String string(25) := '1Geeksforgeeks1';
   
BEGIN 
   dbms_output.put_line(TRIM(Trailing '1' FROM Test_String)); 
   
END; 

Output:

1Geeksforgeeks 

Example-3: Using the BOTH parameter to remove the trim_character from the front as well as the end of input_string.

DECLARE 
   Test_String string(25) := '1Geeksforgeeks1';
   
BEGIN 
   dbms_output.put_line(TRIM(Both '1' FROM Test_String)); 
   
END; 

Output:

Geeksforgeeks 

Example-4: Passing no value in the first parameter of the TRIM function.

DECLARE 
   Test_String string(25) := '  Geeksforgeeks  ';
   
BEGIN 
   dbms_output.put_line(TRIM(' ' FROM Test_String )); 
   
END; 

Output:

Geeksforgeeks 

The TRIM function removes trim_character from both the front and end of the input_string if no value is passed in the first parameter. Advantage: Both the parameters trim_character and trim_source accepts value in any of the datatypes such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.