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. If no specific character is provided, it defaults to removing blank spaces.

Uses of Oracle TRIM Function

• Removing Leading and Trailing Spaces: Clean up strings by removing unwanted leading and trailing spaces.


• Removing Specific Characters: Strip specified characters from the beginning or end of a string.


• Data Formatting: Prepare strings for comparison or display by ensuring consistent formatting.


• Data Cleaning: Ensure strings are free from extraneous characters that may have been inadvertently included during data entry or import.

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:

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