TRIM (SQL)

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

Synopsis

TRIM([end_keyword] [characters FROM] string-expression)

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

TRIM always returns data type VARCHAR, regardless of the data type of the input expression to be trimmed.

Note that leading zeros are automatically stripped from numbers before they are supplied to TRIM or any other SQL function. To retain leading zeros, a number must be specified as a 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-expression.
TRAILING	A keyword that specifies that the characters in characters are to be removed from the end of string-expression.
BOTH	A keyword that specifies that the characters in characters are to be removed from both the beginning and end of string-expression. 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.

You can use the LENGTH function to determine if blank spaces have been stripped from or added to a string.

Characters to Strip

All characters: TRIM returns an empty string if characters contains all the characters in string-expression.
Single quote characters: TRIM can trim single-quote characters if these characters are doubled in both characters and string-expression. Thus, TRIM(BOTH 'a''b' FROM 'bb''ba''acaaa''') returns ‘c’.
Blank spaces: TRIM trims blank spaces from string-expression if characters is omitted. If characters is specified, it must include the blank space character to strip blank spaces.
%List: If string-expression 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.

Arguments

end_keyword

An optional keyword specifying the which end of string-expression to strip. Available values are LEADING, TRAILING, or BOTH. The default is BOTH.

characters

An optional string expression specifying the characters to strip from string-expression. 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’. In this example, the BOTH keyword is optional.

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-expression

The string expression to be stripped. A string-expression 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.

Examples

The following example uses the end_keyword and characters defaults; it removes leading and trailing blanks from "abc". The select-items concatenate ‘^’ to both ends of the string to show blanks.

SELECT '^'||'   abc   '||'^' AS UnTrimmed,'^'||TRIM('   abc   ')||'^' AS Trimmed

returns the strings ^ abc ^ and ^abc^.

The following examples are all valid syntax to strip leading blank spaces from string-expression:

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 examples both remove the character "x" from the beginning and end of "xxxabcxxx", resulting in "abc". The first specifies BOTH, the second takes BOTH as the default:

SELECT TRIM(BOTH 'x' FROM 'xxxabcxxx') AS Trimmed

SELECT TRIM('x' FROM 'xxxabcxxx') AS Trimmed

The following example removes all instances of the characters "xyz" from the end of "abcxzzxyyyyz", resulting in "abc":

SELECT TRIM(TRAILING 'xyz' FROM 'abcxzzxyyyyz') AS Trimmed

The following example trims FullName by stripping all of the letters in FirstName, returning the last name preceded by a blank space. For example FirstName/Fullname ‘Fred’/’Fred Rogers’ returns ‘ Rogers’. In this example, FirstName ‘Annie’ would strip ‘Ann’, ‘Anne’, ‘Ani’, ‘Ain’, ‘Annee’, or ‘Annie’ from LastName, but would not completely strip ‘Anna’ because TRIM is case-sensitive; only ‘A’, not ‘a’, would be stripped.

SELECT TRIM(LEADING FirstName FROM FullName) FROM Sample.Person

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