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 |
+-----------------------------------+-----------+