EXCLUDE - connect_cdc_sqdata - Latest

Connect CDC (SQData) Apply engine

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (SQData)
Version
Latest
Language
English
Product name
Connect CDC (SQData)
Title
Connect CDC (SQData) Apply engine
Copyright
2024
First publish date
2000
Last updated
2024-10-14
Published on
2024-10-14T17:43:00.004977

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

Syntax
EXCLUDE(<data_string>, <include_character_string>, 'ANY' | 'MATCH' )
Parameters and Descriptions
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

The INPUT_STRING contains the a Timestamp value 2023-05-22-16:36:15.123456.
OUTPUT_STRING = EXCLUDE(INPUT_STRING, '0123456789', 'ANY' )

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