TRIM

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

SQL syntax

There are four valid 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:
```
TRIM ( LEADING|TRAILING|BOTH FROM Expression )
```
You can specify the Trim_character without one of the TRIM qualifiers:
```
TRIM (Trim_character FROM Expression )
```
You can specify the Expression without a qualifier or a Trim_character:
```
TRIM ( Expression )
```

Parameters

TRIM has the parameters:

Parameter Description

Parameter	Description
TRIM ( LEADING \| TRAILING \| BOTH [`Trim_character`] FROM `Expression` )	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` is optional. If specified, it represents the CHAR, VARCHAR2, NCHAR or NVARCHAR2 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, then enclose it in single quotes. FROM is required. `Expression` is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If `Expression` is a character literal, then enclose it in single quotes.
TRIM ( `Trim_character` FROM `Expression` )	Removes both leading and trailing instances of `Trim_character` from `Expression`. `Trim_character` is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming `Expression`. Must be only one character. If `Trim_character` is a character literal, then enclose it in single quotes. FROM must follow `Trim_character`. Assumes LEADING \| TRAILING \| BOTH qualifiers have not been specified. `Expression` is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If `Expression` is a character literal, then enclose it in single quotes.
TRIM ( `Expression` )	If you specify `Expression` (without a qualifier or `Trim_character`), then leading and trailing blank spaces are removed from `Expression`. `Expression` is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If `Expression` is a character literal, then enclose it in single quotes.

TRIM (

LEADING | TRAILING | BOTH

[Trim_character]

FROM

Expression )

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 is optional. If specified, it represents the CHAR, VARCHAR2, NCHAR or NVARCHAR2 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, then enclose it in single quotes.

FROM is required.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Trim_character

FROM

Expression )

Removes both leading and trailing instances of Trim_character from Expression.

Trim_character is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming Expression. Must be only one character. If Trim_character is a character literal, then enclose it in single quotes.

FROM must follow Trim_character. Assumes LEADING | TRAILING | BOTH qualifiers have not been specified.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Expression )

If you specify Expression (without a qualifier or Trim_character), then leading and trailing blank spaces are removed from Expression.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

Description

If you specify the LEADING qualifier, TRIM removes any leading characters equal to Trim_character from Expression.
If you specify the TRAILING qualifier, TRIM removes any trailing characters equal to Trim_character from Expression.
If you specify the BOTH qualifier (or no qualifier), TRIM removes leading and trailing characters equal to Trim_character from Expression.
If you specify only Expression, then TRIM removes leading and trailing blank spaces.
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. 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, and NVARCHAR2 types:
- If all the characters in Expression are removed by the TRIM function, then 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, then 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;