TRIM([end_keyword] [characters FROM] string)
|end_keyword||Optional — A keyword specifying the which end of string to strip. Available values are LEADING, TRAILING, or BOTH. The default is BOTH.|
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.
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.
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.
The following example uses the end_keyword and characters 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