A string function that returns a character string with specified leading and/or trailing characters removed.
TRIM([end_keyword] [characters FROM] string)
||Optional A keyword specifying the which end of string to strip. Available values are LEADING, TRAILING, or BOTH. The default is BOTH.
A string expression specifying the characters to strip from string
. Every instance of the specified character(s) is stripped from the specified end(s) until a character not specified here is encountered. Thus TRIM(BOTH 'ab' FROM 'bbbaacaaa')
The FROM keyword is required if characters
is specified. The FROM keyword is permitted (but not required) if end_keyword
is specified and characters
is not specified. If neither of these arguments are specified, the FROM keyword is not permitted.
The string expression to be stripped. A string
can be the name of a column, a string literal, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).
strips the specified characters from the beginning and/or end of a supplied value. By default, stripping of letters is case-sensitive. Character stripping from either end stops when a character not specified in characters
is encountered. The default is to strip blank spaces from both ends of string
The optional end_keyword
argument can take the following values:
||A keyword that specifies that the characters in characters are to be removed from the beginning of string.
||A keyword that specifies that the characters in characters are to be removed from the end of string.
||A keyword that specifies that the characters in characters are to be removed from both the beginning and end of string. BOTH is the default and is used if no end_keyword is specified.
Alternatively, you can use LTRIM
to trim leading blanks, or RTRIM
to trim trailing blanks.
To pad a string with leading or trailing blanks or other characters, use LPAD
Characters to Strip
Blank spaces: TRIM
trims blank spaces if characters
is omitted. If characters
is specified, it must include the blank space character to strip blank spaces.
%List: If string
is a %List, TRIM
can only trim trailing characters, not leading characters. This is because a %List contains leading encoding characters. You must convert a %List to a string to apply TRIM
to leading characters.
returns NULL if either string expression is NULL.
The following example uses the end_keyword
defaults; it removes leading and trailing blanks from "abc":
SELECT TRIM(' abc ') AS Trimmed
The following examples are all valid syntax to strip leading blank spaces from string
SELECT TRIM(LEADING ' abc '),TRIM(LEADING FROM ' def '),TRIM(LEADING ' ' FROM ' ghi ')
The following example removes the character "x" from the beginning of the string "xxxabcxxx", resulting in "abcxxx":
SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx') AS Trimmed
The following example removes the character "x" from the beginning and end of "xxxabcxxx", resulting in "abc":
SELECT TRIM(BOTH 'x' FROM 'xxxabcxxx') AS Trimmed
The following example removes all instances of the characters "xyz" from the end of "abcxxyz", resulting in "abc":
SELECT TRIM(TRAILING 'xyz' FROM 'abcxzzxyyyyz') AS Trimmed
The following example removes the leading letters "B" or "R" from the FavoriteColors values. Note that you must convert a list to a string in order to apply TRIM
to leading characters:
SELECT TOP 15 Name,FavoriteColors,
TRIM(LEADING 'BR' FROM $LISTTOSTRING(FavoriteColors)) AS Trimmed
FROM Sample.Person WHERE FavoriteColors IS NOT NULL