Please enable JavaScript to view this site.

MTRIM examines a data string from left to right and compares the contents to an exclusion character string. If a match is found between the data string and the exclusion character string, the MTRIM function removes the characters which match the exclusion character string. MTRIM will search the entire data string before stopping. MTRIM will remove all characters which match the exclusion string. If a match is not found, then the original data string is returned.

It also provides flexibility regarding the method of comparison by specifying either the ANY or MATCH parameter. Note that for single character matches, ANY and MATCH will return the same results.

A common use of MTRIM is to remove spaces from a string or to remove dashes from phone number.

Category

String

Syntax

MTRIM(data_string, trim_character_string, 'ANY' | 'MATCH' )

 

Parameter Descriptions

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 specify the characters to exclude from 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. Note that 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 scaning 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. Note that this parameter must be enclosed in single quotes.

Example

Examine source field INPUT_STRING. Strip out any characters that exist in the exclusion character string (YA) and map the result to target field OUTPUT_STRING. Assume that INPUT_STRING contains the value XXYYAAABB.

OUTPUT_STRING = MTRIM(INPUT_STRING, 'YA', 'ANY' )

Returns the value XXYAAB and maps the result to the target field.