TRIM

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

SQL syntax

There are four syntax options for TRIM:

You can specify one of the TRIM qualifiers (LEADING or TRAILING or BOTH) with the Trim_character:
```
TRIM ( LEADING|TRAILING|BOTH Trim_character FROM Expression )
```
You can specify one of the TRIM qualifiers (LEADING or TRAILING or BOTH) without the Trim_character. In this case, Trim_character defaults to a blank.
```
TRIM ( LEADING|TRAILING|BOTH FROM Expression )
```
You can specify the Trim_character without one of the TRIM qualifiers, which removes both leading and trailing instances of Trim_character from Expression.
```
TRIM (Trim_character FROM Expression )
```
You can specify the Expression without a qualifier or a Trim_character, which results in leading and trailing blank spaces removed from Expression.
```
TRIM ( Expression )
```

Parameters

TRIM has the parameters:

Parameter	Description
`LEADING \| TRAILING \| BOTH`	`LEADING` `\| TRAILING\| BOTH` are qualifiers to `TRIM` function. `LEADING` removes all leading instances of `Trim_character` from `Expression`. `TRAILING` removes all trailing instances of `Trim_character` from `Expression`. `BOTH` removes leading and trailing instances of `Trim_character` from `Expression`.
`[Trim_character]`	If specified, `Trim_character` represents the `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `CLOB` or `NCLOB` operand or column used for trimming `Expression`. Must be only one character. If you do not specify `Trim_character`, it defaults to a single blank. If `Trim_character` is a character literal, enclose it in single quotes.
`Expression`	`Expression` is the `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `CLOB` or `NCLOB` operand or column to be trimmed. If `Expression` is a character literal, enclose it in single quotes.

Description

If Expression is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. If Expression is of type CLOB, the data type returned is CLOB. If Expression is of type NCLOB, the data type returned is NCLOB. The returned data type length is equal to the data type length of Expression.
If Expression is a data type defined with CHAR length semantics, the returned length is expressed in CHAR length semantics.
If either Trim_character or Expression is NULL, the result is NULL.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Trim_character and Expression. If Expression is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
If Trim_character is of type NCHAR or NVARCHAR2 and Expression is of type CHAR or VARCHAR2, then Trim_character is demoted to CHAR or VARCHAR2 before TRIM is invoked. The conversion of Trim_character could be lost. If Trim_character is not in the database character set, then the query may produce unexpected results.
For CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB and NCLOB types:
- If all the characters in Expression are removed by the TRIM function, the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
- If all the characters in Expression are removed by the TRIM function, the result is the empty string.

Examples

Use TRIM function with qualifier to remove Trim_character '0' from Expression '0000TRIM Example0000':

Command> SELECT TRIM (LEADING '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example0000 >
1 row found.
Command> SELECT TRIM (TRAILING '0' FROM '0000TRIM Example0000') FROM dual;
< 0000TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with qualifier to remove blank spaces. Do not specify a Trim_character. Default value for Trim_character is blank space:

Command> SELECT TRIM (LEADING FROM '    TRIM Example    ') FROM dual;
< TRIM Example     >
1 row found.
Command> SELECT TRIM (TRAILING FROM '    TRIM Example    ') FROM dual;
<     TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH FROM '    TRIM Example    ') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with Trim_character '0'. Do not specify a qualifier. Leading and trailing '0's are removed from Expression '0000TRIM Example0000':

Command> SELECT TRIM ('0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function without a qualifier or Trim_character. Leading and trailing spaces are removed.

< TRIM Example >
1 row found.
Command> SELECT TRIM ('    TRIM Example    ') FROM dual;