Oracle TRIM function
Description
The Oracle TRIM function is used to remove all leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then it is necessary to enclose it in single quotation marks.
Syntax:
TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source )
- When no trim_character is specified, then the default value is a blank space.
- When the only trim_source is specified, then removes leading and trailing blank spaces.
- The maximum length of the value is the length of trim_source.
- If either trim_source or trim_character is null, then the TRIM function returns null.
Parameters:
Name | Description | Data Types |
---|---|---|
trim_character | VARCHAR2 or any data type that can be implicitly converted to VARCHAR2 | |
trim_source | VARCHAR2 or any data type that can be implicitly converted to VARCHAR2 |
Return Value Type
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.
Applies to
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Pictorial Presentation

Examples: Oracle TRIM function
SQL> SELECT TRIM(' removing leading and trailing white spaces ') FROM DUAL; TRIM('REMOVINGLEADINGANDTRAILINGWHITESPACE ------------------------------------------ removing leading and trailing white spaces SQL> SELECT TRIM(' removing leading white spaces') FROM DUAL; TRIM('REMOVINGLEADINGWHITESPA ----------------------------- removing leading white spaces SQL> SELECT TRIM('removing trailing white spaces ') FROM DUAL; TRIM('REMOVINGTRAILINGWHITESPA ------------------------------ removing trailing white spaces SQL> SELECT TRIM(LEADING '0' FROM '000123') FROM DUAL; TRI --- 123 SQL> SELECT TRIM(TRAILING '0' FROM '123000') FROM DUAL; TRI --- 123
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;
Sample Output:
EMPLOYEE_ID TO_CHAR(TRIM(LEADI ----------- ------------------ 103 3-JAN-06 104 21-MAY-07 105 25-JUN-05 106 5-FEB-06 107 7-FEB-07
Previous:
TRANSLATE_USING
Next:
UPPER
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook