TRIM
Syntax
Purpose
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
BOTH
or 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_source
ortrim_character
is null, then theTRIM
function 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
Examples
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