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
orTRAILING
orBOTH
) with theTrim_character
:TRIM ( LEADING|TRAILING|BOTH
Trim_character
FROMExpression
) -
You can specify one of the
TRIM
qualifiers (LEADING
orTRAILING
orBOTH
) without theTrim_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 theTRIM
qualifiers, which removes both leading and trailing instances ofTrim_character
fromExpression
.TRIM (
Trim_character
FROMExpression
) -
You can specify the
Expression
without a qualifier or aTrim_character
, which results in leading and trailing blank spaces removed fromExpression
.TRIM (
Expression
)
Parameters
TRIM
has the parameters:
Parameter | Description |
---|---|
|
|
|
If specified, |
|
|
Description
-
If
Expression
is of typeCHAR
orVARCHAR2
, the data type returned isVARCHAR2
. IfExpression
is of typeNCHAR
orNVARCHAR2
, the data type returned isNVARCHAR2
. IfExpression
is of typeCLOB
, the data type returned isCLOB
. IfExpression
is of typeNCLOB
, the data type returned isNCLOB
. The returned data type length is equal to the data type length ofExpression
. -
If
Expression
is a data type defined withCHAR
length semantics, the returned length is expressed inCHAR
length semantics. -
If either
Trim_character
orExpression
isNULL
, the result isNULL
. -
You can specify
TT_CHAR
,TT_VARCHAR
,TT_NCHAR
, andTT_NVARCHAR
forTrim_character
andExpression
. IfExpression
is of typeTT_CHAR
orTT_VARCHAR
, the data type returned isTT_VARCHAR
. IfExpression
is of typeTT_NCHAR
orTT_NVARCHAR
, the data type returned isTT_NVARCHAR
. -
If
Trim_character
is of typeNCHAR
orNVARCHAR2
andExpression
is of typeCHAR
orVARCHAR2
, thenTrim_character
is demoted toCHAR
orVARCHAR2
beforeTRIM
is invoked. The conversion ofTrim_character
could be lost. IfTrim_character
is not in the database character set, then the query may produce unexpected results. -
For
CHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
andNCLOB
types:-
If all the characters in
Expression
are removed by theTRIM
function, the result isNULL
.
-
-
For
TT_CHAR
,TT_VARCHAR
,TT_NCHAR
andTT_NVARCHAR
types:-
If all the characters in
Expression
are removed by theTRIM
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;