Connect ETL function details are outlined in the Connect ETL functions reference topics of the Connect help.
Consider the following DTL-specific function considerations as they relate to the Connect ETL functions:
| Connect ETL Functions | DTL Specific Function Considerations |
| Extract(value, pattern, matchunit [,extraction]) |
Arguments pattern is a text constant enclosed in single or double quotes, which holds a regular expression that represents the layout of subfields in the value argument. Examples A field Datetime contains the following data: 5/7/1999 3:12:45, 12/22/1999 15:55:12, 4/15/1999 6:10:46. The pattern of components within the field is month followed by a slash, day followed by a slash, year followed by a space, hour followed by a colon, minutes followed by a colon, and seconds. Extract(Datetime, '[0-9]+:[0-9]+:[0-9]+', 'character') returns the time portion of the field. Extract(Datetime, '[0-9]+/[0-9]+/([0-9]+)' , 'character', '\1') returns the year portion of the field. Extract(Datetime, '([0-9]+)/([0-9]+)/([0-9]+)', 'character', 'year: \3 month: \1 day: \2') returns values such as "year: 2000 month: 5 day: 7'. Extract(Datetime, '([0-9]+)/([0-9]+)/([0-9]+)', 'character', '\3\1\2') returns values like '20001228'. |
| FindContainedValue (searchin, searchsettype, searchset[, delimiter]) |
Arguments searchsettype is the case insensitive text constant indicating the type of searchset. Valid values for this field are "LIST", which indicates that searchset is a delimited list, and "FILE", which indicates that searchset is the name of a file or a file alias. searchset is the text constant that contains either of the following:
|
| IfCompares (sequence, text1, operator, text2) |
The IfCompares function compares two ASCII or locale-encoded text values according to a collating sequence, two conditions, or a text value and condition. Arguments sequence is a text constant that holds the collating sequence used in the comparison. operator is a text constant that corresponds to the comparison operator. Return Value A numeric value based on the evaluation of the value or condition. Return value ‘0’ represents false; return value ‘1’ represents true. Example IfCompares(COLLATINGSEQ, OrderNum, OPERATOR, StockNum) returns '1' if OrderNum > StockNum or returns '0' if the comparison is false. |
| IfContainsAny (searchin, searchsettype, searchset[, delimiter]) |
Arguments searchsettype is the case insensitive text constant indicating the type of searchset. Valid values for this field are "LIST", which indicates that searchset is a delimited list, and "FILE", which indicates that searchset is the name of a file or a file alias. searchset is the text constant that contains either of the following:
|
| IfEqualsAny(value, searchsettype, searchset[, delimiter]) |
Arguments searchsettype is the case insensitive text constant indicating the type of searchset. Valid values for this field are "LIST", which indicates that searchset is a delimited list, and "FILE", which indicates that searchset is the name of a file or a file alias. searchset is the text constant that contains either of the following:
|
| IfMatches(value, operator, pattern, matchunit) |
The IfMatch function defines a value or a condition, which evaluates a pattern with the contents of a field. Arguments operator is a text constant enclosed in single or double quotes that corresponds to a match when the values are equal or to no match when the values are not equal. pattern is a text constant enclosed in single or double quotes, which holds a regular expression that is used to match the contents of value. Return Value A numeric value based on the evaluation of the value or condition. Return value ‘0’ represents false; return value ‘1’ represents true. If value evaluates to NULL, the return value is ‘0’. Example IfMatches(ProductDescription, '=', '(computer)|([pP][cC])', 'character' ) returns '1' if the field ProductDescription matches 'computer', or 'PC' in any letter case combination. |
| IfRecordJoined (disposition) |
The IfRecordJoined function defines a value or condition, which evaluates whether a record is matched during the join. Arguments disposition is a text constant that specifies one of the following dispositions for records that are matched and unmatched during a join: Matched, Unmatched, UnmatchedLeft, or UnmatchedRight. Return Value A numeric value based on the evaluation of the value or condition. Return value ‘0’ represents false; return value ‘1’ represents true. Example IfRecordJoined('UnmatchedLeft') IfRecordJoined('UnmatchedRight') |
| IfRecordOrigin (operator, source) |
The IfRecordOrigin function defines a value or a condition, which is based on the origin of the source record. Arguments operator is a text constant that corresponds to equal or to not equal. source is a text constant that holds a file alias. Return Value A numeric value based on the evaluation of the value or condition. Return value ‘0’ represents false; return value ‘1’ represents true. Example IfRecordOrigin("=", "file") IfRecordOrigin("!=", "file") |
| Lookup (aggregatetype, resultfield, lookinsource, condition) |
Arguments lookinsource is the name of a lookup source that can be a file or database table, enclosed in single quotes, or a file alias enclosed in single or double quotes. |
| RegExReplace (searchin, pattern, replacewith) |
Arguments pattern is a text constant of any encoding that contains the expression being searched. replacewith is a text constant of any encoding that can contain subfields. |