All Products
Search
Document Center

TRIM

Last Updated: Jun 18, 2021

The TRIM function deletes leading or trailing characters (or both) from a string.

Syntax

TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source)

Parameters

Parameter

Description

LEADING

The leading characters.

TRAILING

The trailing characters.

BOTH

The leading and trailing characters.

trim_character

The characters to be deleted.

trim_source

The trim source.

The data type of trim_char and trim_source can be VARCHAR2 or data types that can be implicitly converted to VARCHAR2. If you specify LEADING, ApsaraDB for OceanBase deletes all the leading characters that are equal to trim_character. If you specify TRAILING, ApsaraDB for OceanBase deletes all the trailing characters that are equal to trim_character. If you specify BOTH or none of the three parameters, ApsaraDB for OceanBase deletes the leading and trailing characters that are equal to trim_character. If you do not specify trim_character, the default value is a blank space. If you specify only trim_source, ApsaraDB for OceanBase deletes the leading and trailing spaces. If the value that is returned by the function is of the VARCHAR2 data type, the maximum length of the value is trim_source.

Return type

If the data type of trim_source is CHAR or VARCHAR2, the function returns the VARCHAR2 data type.

If the data type of trim_source is NCHAR or NVARCHAR2, the function returns the NVARCHAR2 data type.

If the data type of trim_source is CLOB, the function returns the CLOB data type.

If trim_source or trim_character is NULL, the TRIM function returns NULL.

Examples

Execute the following statement:

SELECT TRIM('X' from 'XXXgao qian jingXXXX'),TRIM('X' from 'XXXgaoXXjingXXXX') text FROM DUAL;

The following query result is returned:

+-----------------------------------+-----------+
| TRIM('X'FROM'XXXGAOQIANJINGXXXX') | TEXT      |
+-----------------------------------+-----------+
| gao qian jing                     | gaoXXjing |
+-----------------------------------+-----------+