Skip to main content
Previous sectionNext section

TRIM

A string function that returns a character string with specified leading and/or trailing characters removed.

Synopsis

TRIM([end_keyword] [characters FROM] string)

Arguments

Argument Description
end_keyword Optional — A keyword specifying the which end of string to strip. Available values are LEADING, TRAILING, or BOTH. The default is BOTH.
characters
Optional — 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') returns ‘c’.
If characters is not specified, TRIM strips blank spaces.
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.
string
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).
The FROM keyword is omitted if both characters and end_keyword are omitted.

Description

TRIM 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:

LEADING A keyword that specifies that the characters in characters are to be removed from the beginning of string.
TRAILING A keyword that specifies that the characters in characters are to be removed from the end of string.
BOTH 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 or RPAD.

Characters to Strip

  • All characters: TRIM returns an empty string if characters contains all the characters in string.

  • Single quote characters: TRIM can trim single-quote characters if these characters are doubled in both characters and string. Thus, TRIM(BOTH 'a''b' FROM 'bb''ba''acaaa''') returns ‘c’.

  • 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.

  • NULL: TRIM returns NULL if either string expression is NULL.

Examples

The following example uses the end_keyword and characters defaults; it removes leading and trailing blanks from "abc":

SELECT TRIM('   abc   ') AS Trimmed
Copy code to clipboard

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   ')
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

See Also