Connect CDC SQData

The EXCLUDE function removes characters from a data string based on a string of inclusion characters. 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

Syntax

EXCLUDE(data_string, inclusion_character_string, 'ANY' | 'MATCH' )

 

Parameter Descriptions

data_string   -  The source data string in character format.

inclusion_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 field/column of a datastore, a literal value or the output of another Function.

ANY | MATCH  -  Denotes the type matching from the left side of a source data string based on the contents of the inclusion character string. Note that these parameters must be enclosed in single quotes.

ANY  -  Indicates that partial matches to the inclusion sting are allowed. ANY is most useful when the inclusion 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.

MATCH  -  Indicates that an exact match to the inclusion string must be made or the character will be removed. EXCLUDE scans the data string removing each charcter that does not match the first letter of the inclusion string, 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 is left in the data string and EXCLUDE continues scaning the data string to see if there is another exact match to keep. All contiguous instances of the inclusion string will be left in the data string. Note that this parameter must be enclosed in single quotes.

Example

Examine field INPUT_STRING and keep only the characters A and B in the result data string before mapping it to target field TGT_STRING. Assume that the field INPUT_STRING contains the value AABBCCDDEE.

TGT_STRING  = EXCLUDE (INPUT_STRING,  'AB',  'ANY')

 

Returns the value AABB.

 

Exclude any characters from the source data string that do not exactly match the character string AB. Map the result to target field TGT_STRING.

TGT_STRING = EXCLUDE ('AABBCCDDEEFF',  'AB',  'MATCH')

 

Returns the value AB.