The EXCLUDE function removes characters from a data string based on a string of inclusion characters therefore It might be better thought of as an "include" function. It also provides flexibility regarding the method of comparison by specifying either the ANY or MATCH parameter. Note that for single character inclusion strings, ANY and MATCH will return the same results. The EXCLUDE is the opposite of the MTRIM function.
Category
String
EXCLUDE(<data_string>, <include_character_string>, 'ANY' | 'MATCH' )
Parameter | Description |
---|---|
data_string | The source data string in character format. |
include_character_string |
One or more characters that specify the characters to retain in the data string (all characters not specified are excluded from the data string). Inclusion characters may be a list of characters enclosed in single quotes, a field/column of a 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 include character string. These parameters must be enclosed in single quotes. |
ANY |
Indicates that partial matches to the include sting are allowed. ANY is most useful when the exclusion string is more than one character. As EXCLUDE scans the data string from left to right, the current character will be removed unless it appears anywhere in the inclusion string. Thus, the inclusion string should be thought of as a list of characters which have no order. EXCLUDE will stop scanning when reaches the end of the data string. |
MATCH |
Indicates that an exact match to the inclusion string must be made. EXCLUDE scans the data string until it finds the first character of the inclusion string removing characters as it goes, then checks the next letter of the data string to see if it matches the next letter of the inclusion string and so on, to the end of the inclusion string. If a full match is found, it starts over after the last matching character. EXCLUDE then repeats the process scanning the data string to see if there is another exact match to include. All contiguous instances of the inclusion string will be left in the data string. |
Example 1
The INPUT_STRING contains the value XXYYAAABB.
OUTPUT_STRING = EXCLUDE(INPUT_STRING, 'YA', 'ANY' )
Returns the value YYAAA and maps the result to the target field.
Example 2
The INPUT_STRING contains the value XXYYAAABB.
OUTPUT_STRING = EXCLUDE(INPUT_STRING, 'YA', 'MATCH' )
Returns the value YA and maps the result to the target field.
Example 3
OUTPUT_STRING = EXCLUDE(INPUT_STRING, '0123456789', 'ANY' )
Returns the value 20230522163615123456 and maps the result to the target field.