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:
- LEADING: This parameter is used to remove the trim_character from the front of input_string.
- TRAILING: This parameter is used to remove trim_character from the end of input_string.
- BOTH: This parameter is used to remove trim_character from the front and end of input_string.
- input_string: It is used to specify the source string.
- 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:
- Oracle 12c
- Oracle 11g
- Oracle 10g
- Oracle 9i
- Oracle 8i
Example-1: Using the LEADING parameter to remove the trim_character from the front of input_string.
DECLARE Test_String string(25) := '1w3wiki1'; BEGIN dbms_output.put_line(TRIM(LEADING '1' FROM Test_String)); END;
Output:
w3wiki1
Example-2: Using the TRAILING parameter to remove the trim_character from the end of input_string.
DECLARE Test_String string(25) := '1w3wiki1'; BEGIN dbms_output.put_line(TRIM(Trailing '1' FROM Test_String)); END;
Output:
1w3wiki
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) := '1w3wiki1'; BEGIN dbms_output.put_line(TRIM(Both '1' FROM Test_String)); END;
Output:
w3wiki
Example-4: Passing no value in the first parameter of the TRIM function.
DECLARE Test_String string(25) := ' w3wiki '; BEGIN dbms_output.put_line(TRIM(' ' FROM Test_String )); END;
Output:
w3wiki
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.