The MTRIM function removes characters from a data string based on a string of exclusion characters. It also provides flexibility regarding the method of comparison by specifying either the ANY or MATCH parameter. Working from left to right, if a match is found between the data string and the exclusion character string, the MTRIM function removes the characters.
A common use of MTRIM is to remove spaces from a string, dashes from phone number or common date punctuation from a formatted date or timestamp.
Category
String
MTRIM(data_string, exclude_character_string, ''ANY' | 'MATCH')| 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 hand side 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 left side of a source data string based on the contents of the exclusion character string. These parameters must be enclosed in single quotes. |
| ANY | Indicates that partial matches to the exclusion sting are allowed. ANY is most useful when the exclusion string is more than one character. As MTRIM scans the data string from left to right, the current character will be removed if it appears anywhere in the exclusion string. Thus, the exclusion string should be thought of as a list of characters which have no order. MTRIM will stop scanning when reaches the end of the data string. |
| MATCH |
Indicates that an exact match to the exclusion string must be made. MTRIM scans the data string until it finds the first character of the exclusion string in the data string, then checks the next letter of the data string to see if it matches the next letter of the exclusion string and so on, to the end of the exclusion string. If a full match is found, it is removed from the data string and MTRIM continues scanning the data string to see if there is another exact match to remove. All contiguous instances of the exclusion string will be removed from the data string. |
Example 1
The INPUT_STRING contains the value XXYYAAABB.
OUTPUT_STRING = MTRIM(INPUT_STRING, 'YA', 'ANY' )Returns the value XXBB and maps the result to the target field.
Example 2
OUTPUT_STRING = MTRIM(INPUT_STRING, 'YA', 'MATCH' )Returns the value XXYAABB and maps the result to the target field.
Example 3
OUTPUT_STRING = MTRIM(INPUT_STRING, '-:.', 'ANY' )Returns the value 20230522163615123456 and maps the result to the target field.