docs.intersystems.com
Caché SQL Reference
TRIM
InterSystems: The power behind what matters   
Search:    
A string function that returns a character string with specified leading and/or trailing characters removed.
Synopsis
TRIM([end_keyword] [characters FROM] string)
Arguments
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
Examples
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
See Also
Help us improve this page