TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotation marks.
-
If you specify
LEADING, then Oracle Database removes any leading characters equal totrim_character. -
If you specify
TRAILING, then Oracle removes any trailing characters equal totrim_character. -
If you specify
BOTHor none of the three, then Oracle removes leading and trailing characters equal totrim_character. -
If you do not specify
trim_character, then the default value is a blank space. -
If you specify only
trim_source, then Oracle removes leading and trailing blank spaces. -
The function returns a value with data type
VARCHAR2. The maximum length of the value is the length oftrim_source. -
If either
trim_sourceortrim_characteris null, then theTRIMfunction returns null.
Both trim_character and trim_source can be VARCHAR2 or any data type that can be implicitly converted to VARCHAR2. The string returned is a VARCHAR2 (NVARCHAR2) data type if trim_source is a CHAR or VARCHAR2 (NCHAR or NVARCHAR2) data type, and a CLOB if trim_source is a CLOB data type. The return string is in the same character set as trim_source.
See Also:
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules, which define the collation TRIM uses to compare characters from trim_character with characters from trim_source, and for the collation derivation rules, which define the collation assigned to the character return value of this function
This example trims leading zeros from the hire date of the employees in the hr schema:
SELECT employee_id, TO_CHAR(TRIM(LEADING 0 FROM hire_date)) FROM employees WHERE department_id = 60 ORDER BY employee_id;EMPLOYEE_ID TO_CHAR(T----------- --------- 103 20-MAY-08 104 21-MAY-07 105 25-JUN-05 106 5-FEB-06 107 7-FEB-07
