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.

My Personal Notes arrow_drop_up

Recommended Posts:

    Article Tags :
    Practice Tags :


    1


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.