TRIM_: Removing a Leading Character, Trailing Character, or Both From a String
The TRIM_ function removes all occurrences of a single character from either the beginning or end of a string, or both.
- Leading and trailing blanks count as characters. If the character you want to remove is preceded (for leading) or followed (for trailing) by a blank, the character will not be removed. Alphanumeric fields that are longer than the number of characters stored within them are padded with trailing blanks.
- The function will be optimized when run against a relational DBMS that supports trimming the character and location specified.
Remove a Leading Character, Trailing Character, or Both From a String
TRIM_(where, pattern, string)
where:
Keyword
Defines where to trim the source string. Valid values are:
- LEADING, which removes leading occurrences.
- TRAILING, which removes trailing occurrences.
- BOTH, which removes leading and trailing occurrences.
Alphanumeric
Is a single character, enclosed in single quotation marks ('), whose occurrences are to be removed from string. For example, the character can be a single blank (‘ ‘).
Alphanumeric
Is the string to be trimmed.
The data type of the returned string is AnV.
Trimming a Character From a String
In the following request, TRIM_ removes leading occurrences of the character ‘B’ from the DIRECTOR field:
TABLE FILE MOVIES PRINT DIRECTOR AND COMPUTE TRIMDIR/A17 = TRIM_(LEADING, 'B', DIRECTOR); WHERE DIRECTOR CONTAINS 'BR' ON TABLE SET PAGE NOPAGE END
The output is:
DIRECTOR TRIMDIR -------- ------- ABRAHAMS J. ABRAHAMS J. BROOKS R. ROOKS R. BROOKS J.L. ROOKS J.L.
TRIM_ removes leading occurrences of the character ‘B’ from DIRECTOR.
TRIM_(LEADING, 'B', DIRECTOR)
For BROOKS R., the result is ROOKS R.
Trimming With Trailing Blanks
The following request trims a trailing period (.) from the director name. The field DIRECTOR has format A17, so there are trailing blanks in most of the instances of the field. To create a field (DIRECTORV) without trailing blanks, SQUEEZ converts the trailing blanks in DIRECTOR to a single blank, then TRIMV removes the remaining trailing blank and stores it with format A17V, so the length of the actual characters is known. Then TRIM_ is called against DIRECTOR and DIRECTORV, creating the fields TRIMDIR (trimmed DIRECTOR) and TRIMDIRV (trimmed DIRECTORV) :
DEFINE FILE MOVIES DIRECTORV/A17V = TRIMV('T', SQUEEZ(17, DIRECTOR, 'A17V'), 17, ' ', 1, DIRECTORV) ; TRIMDIR/A17 = TRIM_(TRAILING, '.', DIRECTOR); TRIMDIRV/A17V = TRIM_(TRAILING, '.', DIRECTORV); END TABLE FILE MOVIES PRINT DIRECTOR TRIMDIR DIRECTORV TRIMDIRV ON TABLE SET PAGE NOPAGE END
The partial output shows that the trimmed DIRECTOR field still has the trailing periods because the period is not the last character in the field. In the trimmed DIRECTORV field, the trailing periods have been removed:
DIRECTOR TRIMDIR DIRECTORV TRIMDIRV -------- ------- --------- -------- SPIELBERG S. SPIELBERG S. SPIELBERG S. SPIELBERG S KAZAN E. KAZAN E. KAZAN E. KAZAN E WELLES O. WELLES O. WELLES O. WELLES O LUMET S. LUMET S. LUMET S. LUMET S