TRIM examines a data string from left to right and then right to left, comparing the contents to an exclusion character string. If a match is found between the data string and the exclusion character string, the TRIM function removes the characters which match the exclusion character string. TRIM starts on the left and continues until the first non-matching character is found and then repeats the process starting from the right. It also provides flexibility regarding the method of comparison by specifying either the ANY or MATCH parameter. Note that for single character exclusion strings, ANY and MATCH will return the same results. Note that TRIM is a combination of the LTRIM and RTRIM functions.
Category
String
Syntax
TRIM(data_string, exclusion_character_string, 'ANY' | 'MATCH' )
Parameters and Descriptions
| Parameter | Description |
|---|---|
| data_string |
The data string in character format. The data string can be a field from a source datastore, a variable, a constant or the result of another Function. |
| exclusion_character_string |
One (1) or more characters that are to be removed from the left and right hand sides of the data string. Exclusion characters may be a field of a source datastore, a literal value or the output of another Function. |
| ANY | MATCH |
Denotes the type of matching from the right side of a source data string based on the contents of the exclusion character string. Note that these parameters must be enclosed in single quotes.
|
Example 1
Examine a source data string and compare it to an trim character string Y. If a match is found, strip out the characters to the left and right of the matching sub-string. Map the result to target field TGT_STRING.
TGT_STRING = TRIM ('XXYAAA', 'Y', 'ANY')
The result Y is returned and maps the result to the target field.
Example 2
Examine a source data string and exclude any characters from the left and right hand side of the source data string that do not exactly match the exclusion character string A. Map the result to target field TGT_STRING.
TGT_STRING = TRIM ('XXYYAAA', 'Y', 'MATCH')
Returns the value XXYY and maps the result to the target field.