Script Functions N-Z
This topic includes information on setting the scope of functions, as well as details on each of the available functions.
NamePart Function
Description | Splits a name field, and returns the name parts to the same field in a different order, or to a different field or fields |
Return | Name parts first, middle, last, either to the same field in a different order or to a different field or fields |
Return (Null) | Error: Incorrect argument count |
Syntax | NamePart([h] [f] [m] [mi] [l] [t], string) |
Parameters | The following parameters can be used in this function: • h - Returns the honorific; for example, Mr. or Ms. If a string literal, enclose in quotation marks. • f - Returns the first name. If a string literal, enclose in quotation marks. • m - Returns the middle name. If a string literal, enclose in quotation marks. • mi - Returns the middle initial. If a string literal, enclose in quotation marks. • l - Returns the last name. If a string literal, enclose in quotation marks. • t - Returns the title; for example, Jr. If a string literal, enclose in quotation marks. • string - This is the search string from the source file. It can be entered either as a specific string or as a field. If a string literal, enclose in quotation marks. |
Remarks | When a field in the source file contains a full name, it is sometimes desirable to either rearrange the name in the same field in the target file. Or split (parse) the full name into parts (honorific, first, middle, last, title) and write each part to a separate field in the target file. You may find that you need a title, honorific or prefix that is not in the list. To find out how to customize the existing NamePart list, see Parsing a Name Field Into Separate Fields. For the supported parts, see below. For additional details, see Parsing a Name Field Into Separate Fields. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
Titles | Titles recognized by the NamePart function include "PhD", "Sr", "Jr", "I", "II", "III", "IV", "V", "MD" and "DO" and null. Also, anything three or more characters in length and ending with a period is considered a title. |
Honorifics | Honorific names recognized by the NamePart function include: "Professor", "Sir", "Doctor", "Mister", "Mr", "Mrs", "Miss", "Dr", "Ms", "Rev" and null. Also, anything that is three or more characters in length and ends with a period is considered an honorific. |
Last Name Prefixes | Last name prefixes recognized by the NamePart function include: "Van", "Van Der", "Von", "Von Der", "de", "la" and null. |
Letters "I" and "V" | The name parser reads the letters "I" and "V" depending on what follows them, since they can signify a first or middle initial or a title, such as: I = "The First" V = "The Fifth" If the letters "I" or "V" are followed by: • A period or comma • First or middle name or any word other than a title Then the they are considered initials. Otherwise, they are treated as a title. |
See Also | |
Example | The following expression parses the "Name" field into separate fields in a target file. The first name, middle name, last name and title (such as Jr.) are all in one field called "Name" in the source file. Place each of the following expressions in a separate field in the target: NamePart("f", FieldAt("/SOURCE/R1/Name"))NamePart("m", FieldAt("/SOURCE/R1/Name"))NamePart("l", FieldAt("/SOURCE/R1/Name"))NamePart(t"", FieldAt("/SOURCE/R1/Name")) This expression rearranges a name in a single field, where the last name is before the first name in a field called "Name" in the source file. In this case, the "first name-last name" order is returned to the target file: NamePart("f l", FieldAt("/SOURCE/R1/Name") ) |
Now Function
Description | Returns the local computer's date/time |
Return | Date that represents the current date and time according to the setting of the computer's system date and time. |
Return (Null) | Not applicable for this function |
Syntax | Now() |
Parameters | None required; however, the parentheses after Now are required. |
Remarks | The Now Function returns a date containing a date and time that are stored internally as a double-precision number. This number represents a date and time from January 1, 100 through December 31, 9999, where January 1, 1900 is 2. Numbers to the left of the decimal point represent the date; numbers to the right represent the time. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns a date/time value to the target field: Now() |
Null Function
Description | Returns a null value |
Return | Null value |
Return (Null) | Not applicable |
Syntax | Null |
Parameters | This function has no required parameters. |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
Example | Example 1: To insert a null value in a field, provide the following expression: Null Example 2: In following example, if the Record number contains a value less than 10, then it returns null. Else, it returns the Record number value. Dim nullValue nullValue = null If FieldAt("/SOURCE_1/R1/Record Number") < 10 Then nullValue Else FieldAt("/SOURCE_1/R1/Record Number") End If |
Oct Function
Description | Print a number to a string in octal representation. |
Return | String representing the octal value of a number. |
Return (Null) | Error: Incorrect argument count |
Syntax | Oct(number) |
Parameters | This function has the following parameters: • number - Any numeric expression. |
Remarks | If number is not already a whole number, it is rounded to the nearest whole number before being evaluated. You can represent octal numbers directly by preceding numbers in the proper range with &O. For example, &O10 is the octal notation for decimal 8. • If number is Null, Oct returns Null • If number is Empty, Oct returns Zero (0) • If number is Any other number, Oct returns Up to 11 octal characters Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Dim MyOct MyOct = Oct (4) 'Returns 4. MyOct = Oct (8) 'Returns 10. MyOct = Oct (459) 'Returns 713. |
Parse Function
Description | Splits (parses) a string of data in one field on a designated character Note: The Parse and UnString functions are interchangeable and use the same syntax. |
Return | Parsed "parts" to a different field or fields. |
Return (Null) | Error: Too few arguments for function |
Syntax | Parse(number, string, character) |
Parameters | This function has the following parameters: • number - Indicates which "part" or section of the parsed data you want to return. "1" indicates the first part, "2" indicates the second part, "3" indicates the third part, etc. • string - Indicates the string expression to parse. If a string literal, enclose in quotation marks. • character - Indicates the character on which to parse. If a string literal, enclose in quotation marks. |
Remarks | The Parse function enables the user to parse the data in one field in the source file and write the "parts" of the data to a field or fields in the target file. Parsing is done on a unique character, such as a space, an asterisk, a dash, etc. It is important to understand that if you parse on a character that is part of the actual data, undesirable results may occur. Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A Function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example parses a field in the source file called "FIELD1" on a space, and writes each of the three "parts" of the parsed data into three fields in the target file: Parse(1, FieldAt("/SOURCE/R1/FIELD1"), " ") Parse(2, FieldAt("/SOURCE/R1/FIELD1"), " ") Parse(3, FieldAt("/SOURCE/R1/FIELD1"), " ") The Parse function is useful for stripping out data, as in the instance below, the word "Or". The source contains data like "Mr. John Q. Smith Or Mrs. John Q. Smith", so the expression finds the "Or" within the data and concatenates the names with a space between them: Parse(1, FieldAt("/SOURCE/R1/Field1"), "Or") & " " & Parse(2, FieldAt("/SOURCE/R1/Field1"), "Or") You can also use hex values within a Parse function expression. The following expression parses the Address field on any blank (hex value 20). The "Chr(&H20)" portion of the expression satisfies the character parameter for the Parse function: Parse(1, FieldAt("/SOURCE/R1/Address") , Chr(&H20)) or UnString(1, FieldAt("/SOURCE/R1/Address") , Chr(&H20)) |
ParseXMLFile Function
Description | Parses an XML document in a file |
Return | DOM object |
Return (Null) | Error: Invalid use of reserved word |
Syntax | ParseXMLFile(filename) |
Parameters | This function has the following parameters: • filename - Name of the xml file to parse. If a string literal, enclose in quotation marks. |
Remarks | The xml file represented by filename may also be a URI. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Dim doc as DOMDocument Set doc = ParseXMLFile("C:\testfile.xml") |
ParseXML Function
Description | Parses an XML document in a string and returns a DOM object |
Return | DOM object with DOM tree of subject file |
Return (Null) | Error: Invalid use of reserved word |
Syntax | ParseXML(xml) |
Parameters | This function has the following parameters: • xml - A variable representing a string that contains an XML document. If a string literal, enclose in quotation marks. |
Remarks | The string is a byte representation of the XML document. The string can contain any valid XML characters. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Dim doc as DOMDocument Dim xml xml="<abc><def>text</def></abc>" Set doc = ParseXML(xml) |
Print Function
Description | Prints specified string in standard output to the display screen |
Return | Not applicable for this function |
Return (Null) | Error: Incorrect argument count |
Syntax | Print([StdOut]) |
Parameters | This function has the following parameters: • StdOut (optional) - Expression or list of expressions resulting in a string to Print. If omitted, a blank line is printed. |
Remarks | Print function takes one required parameter, an expression resulting in a string to print. It is written to StdOut. Output can be a numeric or string expression. If StdOut parameter is not used, put open and close parentheses after Print, such as the following: Print() |
Example | Dim MyText MyText = "Output message to the immediate window." Print(FieldAt("/SOURCE/R1/Status") & MyText) |
RecordAt Function
Description | Translates a record address to a DJRecord object reference |
Return | DJRecord object |
Return (Null) | |
Syntax | RecordAt(path) |
Parameters | This function has the following parameters: • path - Path to a DJRecord object. |
Remarks | You can use any of the DJRecord variable properties with RecordAt, as follows: RecordAt("/SOURCE/R1").property Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns the name of record R1: RecordAt("/SOURCE/R1").Name |
Reject Function
Description | Set a condition under which records are rejected from the transformation or process |
Return | Rejected records are written to a Reject file or table |
Return (Null) | Error: Incorrect argument count |
Syntax | Reject([booleanExpression]) |
Parameters | This function has the following parameters: • booleanExpression - Optional. A boolean or numeric value that determines whether to execute the function. – If this parameter is set to True or any nonzero integer, the function discards the current record. – If this parameter is set to False or 0, the function is not executed – If no value is provided, the function defaults to True. |
Remarks | Configuring a reject connection is optional. To write reject records, you must add an OutputRecord action to a RecordRejected event on a second control link. If you use no reject connection, the transformation discards reject records. See Discard Function for details. |
See Also | |
Example | You must create a reject file and connection string. In this example, if "Field1" is empty, then this record is written to the reject file, not to the target file. All other records are transformed and written to the target file: If FieldAt("/SOURCE/R1/Field1") == "" Then Reject() In the following example, if "Field1" contains the text "General" and if Field2 is greater than or equal to 10050, then this record is written to the Reject file. All other records are transformed and written to the target file: If FieldAt("/SOURCE/R1/Field1") == "General" and (FieldAt("/SOURCE/R1/Field2") >= 10050) then Reject() |
RemoveDirectory Function
Description | Deletes a directory with a specified name |
Return | Not applicable for this function |
Return (Null) | Error: Incorrect argument count |
Syntax | RemoveDirectory(dirName) |
Parameters | This function has the following parameters: • dirName - A string containing the name of the directory to remove. If a string literal, enclose in quotation marks. Note: Use MacroExpand() while using macros to pass the parameters. |
Remarks | An error is returned if a directory cannot be removed. This is usually because user permissions do not allow changes. |
See Also | |
Example | Delete a directory called MyFiles: RemoveDirectory("C:\MyFiles") |
Replace Function
Description | Within a string, replace the first instance of a substring with another. Note: The Sub and Replace functions are interchangeable and use the same syntax. |
Return | See Remarks. |
Return (Null) | Error: Incorrect argument count |
Syntax | Replace(oldString, newString, searchString) |
Parameters | This function has the following parameters: • oldString - The original string that the expression searches for to replace with the newstring. If a string literal, then enclose in quotation marks. • newString - The string that replaces the instance of the oldstring. If a string literal, then enclose in quotation marks. • searchString - The string to be searched. It can be any string expression. If a string literal, enclose in quotation marks. |
Remarks | Replaces the first instance of oldString with newString in an existing string found within one field of data. Escape Special Characters: Replace recognizes all special characters in the "oldstring" parameter, but they must be escaped. See Example 1 for an expression that escapes the special characters "(" and ")". For more information on regular expression and escaping special characters, see Regular Expressions. Limitation: If you attempt to use this function on very large strings (50K - 100K and above), then you may experience performance issues. To avoid long run of your transformations, do not use Replace for very large strings. String-search functions are case-sensitive. A function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Examples | If you want to find the first instance of parentheses and replace them with another character, you need to use the escape character backslash "\". In the following example, the first instance of "(" and ")" in Field1 are replaced by an empty string in the target: Replace("\(", " ", Sub("\)", " ", FieldAt("/SOURCE/R1/Field1"))) This example replaces the first instance of "Avenue" with "Ave." in the Address field: Replace("Avenue", "Ave.", FieldAt("/SOURCE/R1/Address")) |
ReplaceAll Function
Description | Globally replaces every instance of an expression string in one field of data with a specified string. Note: The ReplaceAll and GSub functions are interchangeable and use the same syntax. |
Return | Value specified in newstring (overwrites oldstring) |
Return (Null) | Error: Incorrect argument count |
Syntax | ReplaceAll(oldstring, newstring, string) |
Parameters | This function has the following parameters: • oldstring - The original string that the expression searches for to replace with the newstring. If a string literal, then enclose in quotation marks. • newstring - The string that replaces every instance of the oldstring. If a string literal, then enclose in quotation marks. • string - Any string containing data. If a string literal, then enclose in quotation marks. |
Remarks | The return value of this function is the string specified by the fieldname parameter. All sections that match the regular expression specified as oldstring parameter is replaced by the string specified as the newstring parameter. The String-search functions are case-sensitive. ReplaceAll recognizes all special characters in the oldstring parameter. All special characters must be escaped to be recognized. See Regular Expressions. If you attempt to use this function on very large strings (50K - 100K and above), then you may experience a performance hit. To avoid long run of your transformations, do not use ReplaceAll for very large strings. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The '(' and ')' characters are used for grouping in regular expressions. The expression "(abc)" matches one or more occurrences of the string abc. If you want to search for the literal characters "("and ")", then use a backslash as an escape character. In the following example, the special characters "(" and ")" in Field1 are escaped with the "\" symbol, and replaced by an empty string in the target: ReplaceAll ("\(", " ", ReplaceAll ("\)", " ", FieldAt("/SOURCE/R1/Field1"))) Replace "Avenue" with "Ave." in the Address field: ReplaceAll ("Avenue", "Ave.", FieldAt("Address")) Search "Mon" or "Fri" in ("Field1") of the source file and replace them with "Monday" and "Friday", respectively. If any value is found in ("Field1") other than "Mon" or "Fri", then do not make any changes in the data: T = ReplaceAll ("Mon.", "Monday", FieldAt("/SOURCE/R1/Field1")) ReplaceAll ("Fri.", "Friday", T) You can also nest the two substitutions together: ReplaceAll ("Mon.", "Monday", ReplaceAll ("Fri.", "Friday", FieldAt("/SOURCE/R1/FIELD1"))) If you want to delete blank spaces within a character field, then you must put two spaces before the asterisk in the first parameter and no space between the quotes in the second parameter. "FIELD" is the field name of the character field in your source file from which you want to delete the blank spaces: ReplaceAll (" *", "", FieldAt("/SOURCE/R1/FIELD")) To delete all non alphanumeric characters from a field: ReplaceAll("[^A-Za-z0-9n]","", FieldAt("/SOURCE/R1/Field1")) |
ResetLookup Function
Description | Resets a lookup table or file, re-reading the contents. Should be called after any change of data to enable lookup functions to use new values. |
Return | Contents of the lookup table. |
Return (Null) | Error: Incorrect argument count. |
Syntax | ResetLookup(tablename) or ResetLookup(filename) |
Parameters | This function has the following parameters: • tablename/filename (required) - Name of the lookup table/file. If a string literal, enclose in quotation marks. For example, ResetLookup(“filename”). |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. Tip... All lookup functions in an integration should use the exact same path, including capitalization and direction of slashes. Best practice is to use a macro in all cases so that the path is entered only once and therefore always identical. |
See Also | |
Example | The following expression tells Map Designer to reread the Customer_No lookup file: ResetLookup("C:\Temp\Customer_No.txt") |
Right Function
Description | Reads and returns the rightmost characters of a string. |
Return | Rightmost characters of a string |
Return (Null) | Error: Incorrect argument count |
Syntax | Right(string, length) |
Parameters | This function has the following parameters: • string (required) - Any string expression. If a string literal, enclose in quotation marks. • length (required) - A Long expression indicating how many characters to return. It must be between 0 and approximately 65,500, inclusive. If length is 0, then the return value is a zero-length string. If length is greater than or equal to the number of characters in string, then the entire string is returned. |
Remarks | Right returns a string. To find the number of characters in string, use Len(string). Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Example 1: This example returns the three rightmost characters of the source field "Field1": Right(FieldAt("/SOURCE/R1/Field1"), 3) If field1 contains "Right" Returns: ght Example 2: This function returns rightmost digit: Right(1234,2) Returns: 34 |
RightB Function
Description | Reads and returns the rightmost bytes of a string |
Return | Rightmost bytes of a string |
Return (Null) | Error: Incorrect argument count |
Syntax | RightB(string, length) |
Parameters | This function has the following parameters: • string (required) - Any string. If a string literal, enclose in quotation marks. • length (required) - The length of bytes to return. It must be between 0 and 255, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the bytes in string, the entire string is returned. |
Remarks | RightB returns a String. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns the 5 rightmost bytes of the source field "FirstName": RightB(FieldAt("/SOURCE/R1/FirstName"),5) |
Rnd Function
Description | Returns a random number |
Return | This function returns a Single value less than 1 but greater than or equal to 0 (zero). |
Return (Null) | Error: Incorrect argument count |
Syntax | Rnd(number) |
Parameters | This function has the following parameters: • number - Any valid numeric expression. |
Remarks | The value of number determines how Rnd generates a random number. See the table below: Value of number - Number returned • < 0 - The same number every time, as determined by number. • > 0 - The next random number in the sequence. • = 0 - The number most recently generated. • number omitted - The next random number in the sequence. This function does not generate random numbers. Instead, like most random number generators, it starts off with a seed value that is used to calculate the next psuedo-random number. One of the benefits of using a seed value is that using the same seed value can reproduce the sequence of random numbers. This is important for testing systems that rely on the random number generators. If you need more random values, you can begin the seed value with one generated by the system clock. Caution This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Rnd(37) Place this expression in the BeforeTransformation event, so it is only executed once at the beginning of the transformation: Rnd(10000 * (Fix(Now()) - Now())) |
Round Function
Description | Round a number |
Return | Number rounded to a specified number of decimal places |
Return (Null) | Error: Too few arguments for function |
Syntax | Round(expression [, numdecimalplaces]) |
Parameters | This function has the following parameters: • expression (required) - Numeric expression being rounded.Enclose in quotation marks only if it is a string literal. • numDecimalPlaces (optional) - Number indicating how many places to the right of the decimal are used in rounding. If omitted, integers are returned by the Round function. |
Remarks | The Round function rounds a floating- or fixed-point decimal to a specified number of places. Although the Round function is useful for returning a number with a specified number of decimal places, you can't always predict how it will round when the rounding digit is a 5. Rounding of a number depends on the internal binary representation of that number. See example. When the digit being rounded is 5, rounding is based on the previous digit. If the digit that precedes 5 is even, the number is preserved. If the digit that precedes 5 is odd, the number is rounded up. The rounding methods always preserve even numbers, while odd numbers are changed to the next highest even number. You can use the Option Math Precise Statement and Option Math Strict Statement to force numbers to be represented as decimal values where possible. These statements eliminate the imprecision of binary floating point value for a performance trade-off. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Both of the following statements return 1.234: Round(1.2335,3) Round(1.2345,3) |
RTrim Function
Description | Trims trailing spaces from a string. |
Return | Specified string with trailing (rightmost) spaces removed |
Return (Null) | Error: Incorrect argument count |
Syntax | RTrim(stringexpr) |
Parameters | This function has the following parameters: • stringexpr - Any expression containing numeric or text characters. If a string literal, enclose in quotation marks. |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example removes the trailing spaces from the string "TestString ": RTrim("TestString ") Result: "TestString" |
Search Function
Description | Returns the position of the first occurrence of one string within another string |
Return | Position of the first occurrence of one string within another string |
Return (Null) | Error: Too few arguments for function |
Syntax | Search(start, string, subString, ignoreCase) |
Parameters | This function has the following parameters: • start - The position to start searching at. • string - The string to search. • subString - Search within the string. The substring starts at a specified character position and continues to the end of the string. • ignoreCase - Specifies whether to ignore case. The supported values are: – 0 - Do not ignore – 1 - Ignore Default value is 0. |
Remarks | If substring is found within string, Search returns the position at which the match was found. If substring is zero-length, then start is returned. If start is greater than string, string is zero-length, or substring cannot be found, then Search returns 0. If either string expression is null, the function returns a null. If start or compare is null, an error occurs. String-search functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument compare, and you do not use the optional compare argument, then the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Examples | This example illustrates how you can find the position of a dollar sign ($) within source field "Field1". It begins the search in the first byte of data in "Field1" and the search is not case sensitive. Dollar sign is at the 12th position of the string: search(1, FieldAt("/SOURCE/R1/Field1"), "$", 1) Applied to a field containing: Sale price $14.00 Returns: 12 |
Second Function
Description | Convert clock time to second |
Return | Integer between 0 and 59, inclusive, which represents the second of the day corresponding to the time provided as an argument |
Return (Null) | Error: Incorrect argument count |
Syntax | Second(dateString) - if containing time value Or Second(TimeString) |
Parameters | This function has the following parameters: • dateString - If containing time value. Or • TimeString - Any value or expression that can represent a date/time. |
Remarks | Numbers to the left of the decimal point in number represent the date; numbers to the right represent the time. Negative numbers represent dates prior to December 30, 1899. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Examples | This example returns the second portion of the current system time (if system time is 1:12:30, "30" is returned to the target: Second(Now()) The example below returns the second portion from the source "Second". The TimeValue function defines each time to be time values, while the Second function defines those values as minutes: a = TimeValue(Trim(FieldAt("/SOURCE/R1/Second"))) Second(a) |
Serial Function
Description | Returns the next serial value. Note: The Serial and Increment functions are interchangeable and use the same syntax. |
Return | Next serial value of a numeric expression |
Return (Null) | Error: Incorrect argument count |
Syntax | Serial(number) |
Parameters | This function has the following parameters: • number - Any valid numeric expression. |
Remarks | The Serial function returns the next serial value. If number is provided, it sets the starting serial number. Notice that Serial returns the next serial value. This means number must be entered as the desired starting value less one. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example writes an incremental number in a field for every record, starting with the number 1: =Serial(0) The following example writes an incremental number in a field for every record, starting with the number 223: Serial(222) |
SetTimeZone Function
Description | Returns the same date and time value with its time zone set to the specified time zone. If the time zone is not specified, the local time zone is used. The time zone is specified as the number of seconds offset from UTC. |
Return | See description. |
Return (Null) | Error: Invalid use of NULL. |
Syntax | SetTimeZone(dateString, [timeZone]) |
Parameters | • dateString - An actual date value or an expression that evaluates to a date value. • timeZone - (optional) The number of seconds offset from UTC. If this parameter is not specified, the local time zone is used. |
Remarks | Note: This function does not change the actual date time value, it changes only the time zone. You can use the DateValMask Function to define the date mask in your expressions. |
See Also | |
Example | For example if the specified date and time value is set to “5/01/2022 13:34:47” in your local time zone, and the specified time zone is 0 (UTC), the result will be a date and time value set to “5/01/2022 13:34:47”, but in UTC. =SetTimeZone(“5/01/2022 13:34:47”, 0) |
Sgn Function
Description | Find the sign of a supplied number |
Return | Value indicating the sign of a number |
Return (Null) | Error: Incorrect argument count |
Syntax | Sgn(number) |
Parameters | This function has the following parameters: • number - Any valid numeric expression. |
Remarks | The number parameter determines the value returned by the Sgn function: • If number > 0, Sgn(number) returns 1. • If number = 0, Sgn(number) returns 0. • If number < 0, Sgn(number) returns -1. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This expression returns a –1, since the result is < 0: Sgn(0-1) This expression returns a 1, since the result is > 0: Sgn(0+2) |
Shell Function
The Shell function is available only if your administrator has enabled the ability to call executables.
Description | Run an external command, specified on command line |
Return | When wait is false, other processes can start before the executable has finished, in which case it returns 0. Otherwise the return is the exit code of the program being run. |
Return (Null) | Error: Too few arguments for function |
Syntax | Shell(command-line [, style] [, wait]) |
Parameters | This function has the following parameters: • command-line (required) - The complete path of the executable to run. If a string literal, enclose in quotation marks. • style (required only with wait) - Value to set the style of window in which to run the program. If omitted, Shell uses minimized focus (value 2), which starts the program minimized and with focus. – 0 - Hidden – 1 - Normal focus – 2 - Minimized focus – 3 - Maximized focus – 4 - Normal; no focus; window is restored to its most recent size and position; currently active window remains active – 5 - Minimized; no focus; currently active window remains active • wait (optional) - Boolean 0 (false) or -1 (true) to indicate whether Shell waits for completion of the program. If omitted, false by default. |
Remarks | Can run any executable program. If you run a .bat file, you cannot use the following syntax: Shell("file.bat") Instead, use the following: Shell("cmd /c file.bat") If you are calling a shell script, such as .bat or .sh, insert @echo off as the first line in the script to prevent the opening of a command prompt window on the server. Attempting to open a command window on server may interfere with the running of the script. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Shell("C:\Program Files\TextPad 4\TextPad.exe",1) |
SignFlip Function
Description | Moves the sign operator from the front of a string to the end, or from the end of the string to the front |
Return | Numeric string with the sign moved to the opposite end of the supplied numeric string |
Return (Null) | Error: Incorrect argument count |
Syntax | SignFlip(string) |
Parameters | This function has the following parameters: • string - String with a sign operator (+, -) at the front of the string or at the end of the string. If a string literal, enclose in quotation marks. |
Remarks | If no sign operator (+, -) exists in the string, the function returns the string as-is. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | SignFlip("-36.09") |
Sleep Function
Description | Put a program into a wait state for a specific number of milliseconds |
Return | Not applicable for this function |
Return (Null) | Error: Incorrect argument count |
Syntax | Sleep(milliseconds[,allowInterrupt]) |
Parameters | This function has the following parameters: • milliseconds (required) - The number of milliseconds to sleep (1000 milliseconds = 1 second). No value is returned. • allowInterrupt (optional) - A boolean or integer value that determines whether the sleep is interrupted if the job is stopped. – If this parameter is set to True or any nonzero integer, the sleep is interrupted. – If this parameter is set to False or 0, the stop command takes effect after the full sleep time specified. – If no value is provided, the function defaults to True. |
Remarks | You can use this function when you need a delay when running a transformation or process. Since there is no return value, if you want to monitor this function, you can use the LogMessage function to determine when the delay has terminated. Sleep is more accurate than using a do-nothing loop and waiting for a certain amount of time to pass. |
See Also | |
Example | This expression causes your program to sleep for 3,500 milliseconds: Sleep(3500) This expression causes a 20-second sleep that cannot be interrupted: Sleep(20000,False) |
SortArray Function
Description | Sort an array |
Return | Not applicable for this function |
Return (Null) | Error: Too few arguments for function |
Syntax | SortArray(array [, ignorecase] [, rev] [, style]) |
Parameters | This function has the following parameters: • array (required) - An array variable • ignoreCase (optional) - Ignore case sensitivity • rev (optional) - Sorting is in reverse order • style (optional) - Sort multidimensional arrays as column-major |
Remarks | The first parameter must be an array variable (if not, a TYPEMISMATCH error is returned). If ignoreCase is true, sorting of string values will ignore case. This is an optional parameter, and its default value is False. If rev is true, sorting is in reverse order (from largest to smallest). This is an optional parameter, and its default value is False. The style parameter default is 0. If this parameter is omitted or unknown, the return is sorted as a one-dimensional array. When the style parameter is set to 1, the multidimensional array is sorted as column-major. The column-major sort returns results ordered by the first value in a row or plane. This value determines the position of the entire row or plane. |
Example | The following expression sorts MyArray by the first element: SortArray(MyArray) |
Space Function
Description | Returns a string consisting of a specified number of spaces. |
Return | Blank spaces specified by number |
Return (Null) | Error: Incorrect argument count |
Syntax | Space(number) |
Parameters | This function has the following parameters: • number - Specifies the number of spaces you want in the string. It can be any number between 0 and approximately 65,000, inclusive. |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example adds three spaces before the data in source field "Field1": Space(3) & FieldAt("/SOURCE/R1/Field1") This example concatenates "Field1" and "Field2" source fields, and adds one (1) space between the concatenated data in the same target field: FieldAt("/SOURCE/R1/Field1") & Space(1) & FieldAt("/SOURCE/R1/Field2") This example creates a report title with 65 spaces before the text begins: Space(65) & "Accounts Receivables Report" |
Split Function
Description | Splits a string into an array of substrings based on the specified delimiter. |
Return | Zero-based, one-dimensional array containing a specified number of substrings |
Return (Null) | Error: Invalid expression syntax |
Syntax | Split(expression[,delimiter][, limit][, compare]) |
Parameters | This function has the following parameters: • expression (required) - String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data. • delimiter (optional) - String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned. • limit (optional) - Number of substrings to be returned; -1 indicates that all substrings are returned. • compare (optional) - Numeric value indicating the kind of comparison to use when evaluating substrings. A value of 0 performs a binary comparison, while a value of 1 will perform a textual comparison. |
Remarks | You can assign arrays to arrays with this function. Best Practice — The Split function is similar to the Parse function, however, Split is superior in performance. The Parse function has to run every time it is used; in contrast, after one Split, you can directly address the results from the array in memory from that point on. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Declare variable MyArray, and set it equal to the field where the values are located: Dim MyArray(1) MyArray = Split(FieldAt("/SOURCE/R1/Field1"), " ") If Field1 contains "John Doe" Returns: MyArray(0) = "John" MyArray(1) = "Doe" |
Stop Function
Description | Causes the transformation to stop based on a specified condition. Note: In previous versions of Actian DataConnect, this function was known as Abort Function. Any existing artifacts using the Abort Function will still be supported from the Actian DataConnect Engines and Editors. |
Return | Not applicable; stops the transformation |
Return (Null) | Not applicable; stops the transformation. |
Syntax | Stop(number) |
Parameters | This function has the following parameters: • number - Any valid numeric expression. |
Remarks | Used in a conditional expression. If the number parameter is nonzero (or if no number is supplied), this function sets a flag to stop the transformation. When a Stop is reached, no changes are applied to the target. Use the Terminate Function instead if you want to apply changes to the target and then continue with the process. |
See Also | |
Example | The following expression converts the data in the ("Account_No") field (along with the remainder of the mapped data. If the rightmost character (digit) of the value in the ("Account_No") field is zero (0), then the flag is reset and stop does not occur: If Right(FieldAt("/SOURCE/R1/Account_No"),1) == 0 Then Stop() Else FieldAt("/SOURCE/R1/Account_No") End If |
Str Function
Description | Convert a numeric value to a text string. |
Return | String representation of the value of a numeric expression |
Return (Null) | Error: Incorrect argument count |
Syntax | Str(number) |
Parameters | This function has the following parameters: • number - Any numeric expression. |
Remarks | When numbers are converted to text, a leading space is always reserved for the sign of number. If number is positive, the string returned by Str contains a leading blank and the plus sign is implied. In contrast, the Format Function does not include a leading blank space. Str returns a String. Use Str to convert simple numeric values to strings. Use the Format function to convert numeric values you want formatted as dates, times, or currency or in other user-defined formats. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Example 1: Returns the number 459 as a string representation. The Hex Browser interprets the string 459 as "20 34 35 39". "20" represents the leading space reserved for the sign of the number: Str(459) Example 2: Str converts date into string data type. The returned string can be used in SubString function as follows: =var1 = Str(CDate("28-02-1971")) SubString(var1, 1, 5) This expression returns 28-02 in a text string data type. |
StrComp Function
Description | Compare two string arguments Note: The StrComp and Compare functions are interchangeable and use the same syntax. |
Return | Value that represents the result of a comparison of two string arguments |
Return (Null) | Error: Too few arguments for function |
Syntax | StrComp(strexpr1,strexpr2[,[compare]) |
Parameters | This function has the following parameters: • strexp1 (required) - The first string argument. • strexp2 (required) - The second string argument. • compare (optional) - Specifies the string-comparison method. This value must be 0 or 1. If compare is 0, the string comparison is case-sensitive. If compare is 1, string-comparison is not case-sensitive. |
Remarks | This function returns a value that indicates the relationship between the string expressions. If strexpr1 is less than strexpr2, -1 is returned. If strexpr1 equals strexpr2, 0 is returned. If strexpr1 is greater than strexpr2, 1 is returned. Be careful to observe the case when using a function that performs a string search. String-search functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional parameter "compare", and you do not use the optional compare parameter, the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example compares "fed" and "FED" and requires the string comparison to be case-sensitive by indicating "(0)" as the compare. It returns "1", since the text strings are mixed case: StrComp("fed", "FED", (0)) If a (1) compare is substituted in the expression above, a 0 is returned to the target, indicating that the text strings are the same (case-sensitivity is ignored). |
String Function
Description | Returns a string consisting of a specified number of occurrences of a specified character. |
Return | String consisting of a specified number of occurrences of a specified character. |
Return (Null) | Incorrect argument count for function. |
Syntax | String(number, string) |
Parameters | This function has the following parameters: • number - Numeric expression indicating the length of the returned string. Number must be between 0 and 65,535 inclusive. • string - String expression where the first character is used to build the return string. To convert numeric values greater than 255 to an ANSI code between 0 and 255, String uses the remainder after the value is divided by 256 (argument Mod 256). |
Remarks | String returns a String. Use String to create a string that consists of one character repeated over and over. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | In the following example, String returns a string consisting of 8 asterisks by replicating the character provided by the second argument: String(8, "*") |
StrReplace Function
Description | Replaces one literal string with another literal string |
Return | newString argument |
Return (Null) | Error: Too few arguments for function |
Syntax | StrReplace(oldString, newString, searchString [,occurrences]) |
Parameters | This function has the following parameters: • oldString (required) - The literal string to be replaced. If a string literal, enclose in quotation marks. • newString (required) - The literal string to replace oldString. If a string literal, enclose in quotation marks. • searchString (required) - The search string. May be entered as a field name. If a string literal, enclose in quotation marks. • occurrences (optional) - Controls the number of strings replaced. If a string literal, enclose in quotation marks. If the occurrences parameter is omitted, then all occurrences of oldString are replaced with newString, since global replaces are probably one of the most common string operations. |
Remarks | Be careful to observe case when using a function that performs a string-search. String-search functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default. The StrReplace function is similar to Sub/GSub functions with the following differences: • The replacement text (oldString and newString) is expressed as literal strings only. No regular expression metacharacters can be used. For examples of these metacharacters, see Regular Expressions. To use metacharacters in an expression, use the GSub Function instead. • The number of strings replaced can be controlled by the optional fourth parameter occurrences; It supports the substitution of individual binary characters. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example returns "ABCdefg" – all occurrences. StrReplace("abc", "ABC", "abcdefg") The following example returns "ABC/xyz/ABC/xyz/ABC" – all occurrences. StrReplace("abc", "ABC", "abc/xyz/abc/xyz/abc") The following example returns "ABC" – all occurrences. StrReplace("abcdefg", "ABC", "abcdefg") The following example returns "Texas" for all occurrences of "TX" in ("/SOURCE/R1/FIELDNAME") StrReplace("TX", "Texas", FieldAt("/SOURCE/R1/FIELDNAME")) The following example replaces nulls with nothing in all occurrences. StrReplace(Chr(00), "", FieldAt("/SOURCE/R1/FIELDNAME")) The following example searches for the words "Drive", "Lane", "Avenue", "Street", and "Road" in Field1 in the source file, and replaces each, respectively, with the abbreviations "Dr", "Ln", "Ave", "St", and "Rd" in the target file: StrReplace("Drive", "Dr", StrReplace("Lane", "Ln", StrReplace("Avenue", "Ave", StrReplace("Street", "St", StrReplace("Road", "Rd", FieldAt("/SOURCE/R1/Address")))))) |
StrReverse Function
Description | Reverse the order of a string |
Return | String in which the character order of a specified string is reversed |
Return (Null) | Error: Incorrect argument count |
Syntax | StrReverse(string) |
Parameters | This function has the following parameters: • string - The string whose characters are to be reversed. If a string literal, enclose in quotation marks. |
Remarks | If expression is a zero-length string (""), a zero-length string is returned. If expression is null, an error occurs. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
Example | The following expression returns the string gnirts (the string example "string" reversed): StrReverse("string") |
Sub Function
Description | Within a string, replace the first instance of a substring with another. Note: The Sub and Replace functions are interchangeable and use the same syntax. |
Return | See Remarks. |
Return (Null) | Error: Incorrect argument count |
Syntax | Sub(oldString, newString, searchString) |
Parameters | This function has the following parameters: • oldString - The original string. If a string literal, enclose in quotation marks. • newString - The replacement string. If a string literal, enclose in quotation marks. • searchString - The string to be searched. Can be any string expression. If a string literal, enclose in quotation marks. |
Remarks | Sub replaces the first instance of oldString with newString in an existing string found within one field of data. Escape Special Characters: Sub recognizes all special characters in the "oldstring" parameter, but they must be escaped. See Example 1 for an expression that escapes the special characters "(" and ")". For more information on regular expression and escaping special characters, see Regular Expressions. Limitation: If you attempt to use this function on very large strings (50K and above), you may experience a performance hit. To avoid long run times of your transformations, do not use Sub on very large strings. Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Examples | If you want to find the first instance of parentheses and replace them with another character, you need to use the escape character backslash "\". Below, the first instance of "(" and ")" in Field1 are replaced by an empty string in the target: Sub("\(", " ", Sub("\)", " ", FieldAt("/SOURCE/R1/Field1"))) This example replaces the first instance of "Avenue" with "Ave." in the Address field: Sub("Avenue", "Ave.", FieldAt("/SOURCE/R1/Address")) |
SubString Function
Description | The SubString() function extracts some characters from a string |
Return | Returns a part of the string |
Return (Null) | Incorrect argument count for function |
Syntax | SubString(string, start, length) |
Parameters | This function has the following parameters: • string - The string to extract from. • start - The start position. The first position in string is 1. • length- The number of characters to extract. Must be a positive number. Note: Length parameter is optional. |
Remarks | <<Need Information>> |
See Also | |
Example | The following example returns "4 character" from Field1 stating from 7th position. SubString(FieldAt("/SOURCE_1/R1/Field1"), 7, 4) Where Field1: 44060-1930 Returns: 1930 |
Switch Function
Description | Evaluate a list of expressions |
Return | Value or an expression associated with the first expression in the list that is True |
Return (Null) | Error: Incorrect argument count |
Syntax | Switch(strexpr1, str1 [, strexpr2, str2...strexpr7, str7]) |
Parameters | This function has the following parameters: • strexpr - Expression you want to evaluate. A maximum of seven instances of this parameter is llowed. If a string literal, enclose in quotation marks. • str - Value or expression that is returned if the corresponding strexpr is True. If a string literal, enclose in quotation marks. Switch("strexp" ,"str1"). |
Remarks | The Switch function evaluates the string expression in the strexpr list and returns the contents of the first str argument whose corresponding strexpr expression evaluates True. For example, if strexpr1 is True, Switch returns str1. If strexpr1 is False, but strexpr2 is True, Switch returns str2, and so on. Switch returns a null if: • None of the strexpr expressions is True. • The leftmost strexpr that is True has a corresponding str that is null. Switch evaluates all of the expressions, even though it returns only one of them. For this reason, watch for undesirable results. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | In this example, the source field ("City") is evaluated for the text strings "Mad" and "Mon". If "Mad (Madrid)" is True, "Spain" is returned. If "Mon (Monterrey)" is true, "Mexico" is returned. If neither of these conditions are true, "US" is returned to the target field: Switch(FieldAt("/SOURCE/R1/City") Like "Mad", "Spain", FieldAt("/SOURCE/R1/City") Like "Mon", "Mexico", FieldAt("/SOURCE/R1/City") <> "Mad" OR "Mon", "US") |
Terminate Function
Description | Causes the transformation to terminate based on a specified condition. |
Return | Not applicable; terminates a transformation pending the outcome of a user-defined integer expression. |
Return (Null) | Error: Incorrect argument count |
Syntax | Terminate(number) |
Parameters | This function has the following parameters: • number (optional) - Any valid numeric expression. |
Remarks | Used in a conditional expression. If the number parameter is nonzero (or if no number is supplied), this function sets a flag to terminate the transformation. Use the Terminate function to end the transformation if an event occurs. This function does not roll back changes; it acts as if the transformation has reached the source file. When a Terminate is reached, the transformation is regarded as having been completed successfully and changes are committed to the target. To stop a transformation and roll back changes to the target, use the Stop Function instead. |
See Also | |
Example | The following function can be written in a RecordStarted event to terminate the transformation once all Account No records up to 10026 (tutor1.asc) are written: If FieldAt("/SOURCE/R1/Account No") > 10026 Then Terminate() Else, FieldAt("/SOURCE/R1/Account No") End If |
TestMask Function
Description | Strips non-digit characters from a string and indicates whether the string would actual wholly convert to a number. |
Return | Boolean value. A "-1" is returned for True result. A "0" is returned for a False result. |
Return (Null) | Error: Incorrect argument count |
Syntax | TestMask(string, mask) |
Parameters | This function has the following parameters: • string - A string that can be converted to a numeric value. • mask - A valid string expression that represents display format characters. |
Remarks | The mask is treated as a (very) short list of characters (comma (,), dollar sign ($), percent (%), and exponent ('e' or 'E') that should not terminate processing. These characters can occur multiple times except for the exponent indicator, which can only occur once. In addition, the dash/negative sign is allowed once, as is the dot/period. Note that there is no requirement that the dash/negative sign can only appear at the beginning of the string. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | TestMask("10/12/20", "######") results in converting string '10' with result 10.000000 (TRUE) TestMask("2010-08-17", "########") results in converting string '2010-08' with result 2010.000000 (FALSE) TestMask("999-99-9999", "#########") results in converting string '999-99' with result 999.000000 (FALSE) TestMask("1-999-999-9999", "1-##########") results in converting string '1-999' with result 1.000000 (FALSE) TestMask("08-17-2010", "########") results in converting string '08-17' with result 8.000000 (FALSE) TestMask("$2,600", "####") results in converting string '' with result 0.000000 (TRUE) TestMask("10/08/17", "######") results in converting string '10' with result 10.000000 (TRUE) TestMask("abcd", "########") results in converting string '' with result 0.000000 (TRUE) TestMask("a,bcd", "#-###") results in converting string '' with result 0.000000 (TRUE) TestMask("a,bcd", "#/###") results in converting string '' with result 0.000000 (TRUE) TestMask("-99", "##") results in converting string '-99' with result -99.000000 (TRUE) |
Time Function
Description | Write current system time. |
Return | Current system time |
Return (Null) | Error: Incorrect argument count |
Syntax | Time( ) |
Parameters | None are required, however, parentheses are required to follow the function name. |
Remarks | Time returns a time stored as the fractional part of a double-precision number. Time returns an eight-character string in the form of hh:mm:ss, where hh is the hour (00 - 23), mm is the minute (00 - 59), and ss is the second (00 - 59). A 24-hour clock is used; therefore, 6:00 P.M. displays as 18:00:00. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This function returns the current system time: Time() |
TimeSerial Function
Description | Constructs a time value from user-specified hour, minute, second values. |
Return | Time serial for a specific hour, minute, and second |
Return (Null) | Error: Incorrect argument count |
Syntax | TimeSerial(hour, minute, second) |
Remarks | This function has the following parameters: • hour - An hour between 0 (12:00 AM) and 23 (11:00 PM), inclusive, or a numeric expression. • minute - A minute between 0 and 59, inclusive, or a numeric expression. • second - A second between 0 and 59, inclusive, or a numeric expression. |
Remarks | To express a specific time, such as 10:30:00, the range of numbers for each TimeSerial parameter should conform to the accepted range of values for this designation. These values are 0 through 23 for hours, and 0 through 59 for minutes and seconds. Relative times for each parameter can also be specified by using a numeric expression that represents the number of hours, minutes, and seconds before or after a certain time, such as: TimeSerial(12 - 1, 0 - 30, 0) for 10:30:00 AM. (Thirty minutes before one hour before noon.) TimeSerial returns a time stored internally as a double-precision fractional number between 0 and .99999. This number represents a time between 00:00:00 and 23:59:59, or 12:00:00 AM and 11:59:59 PM, inclusive. If the time specified by the three parameters falls outside the acceptable range of times, an error occurs. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns the time "10:30:00 AM". (One hour and 30 minutes before noon.): TimeSerial(12 - 1, 0 - 30, 0) |
TimeStamp Function
Description | Returns the local computer's date/time |
Return | Date that represents the current date and time according to the setting of the computer's system date and time. |
Return (Null) | Not applicable for this function |
Syntax | TimeStamp() |
Parameters | None required; however, the parentheses after TimeStamp are required. |
Remarks | Returns a date containing a date and time that are stored internally as a double-precision number. This number represents a date and time from January 1, 100 through December 31, 9999, where January 1, 1900 is 2. Numbers to the left of the decimal point represent the date; numbers to the right represent the time. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns a date/time value to the target field: TimeStamp() |
TimeValue Function
Description | Find time value from a string argument |
Return | Time represented by a string argument |
Return (Null) | Error: Incorrect argument count |
Syntax | TimeValue(strexpr) |
Parameters | This function has the following parameters: • strexpr - A string representing a time from 00:00:00 (12:00:00 AM) through 23:59:59 (11:59:59 PM). If a string literal, enclose in quotation marks. |
Remarks | Valid times can be entered using a 12-hour or 24-hour clock. In other words, both "5:30 PM" and "7:30:00" are valid arguments. TimeValue returns a time stored internally as a double-precision fractional number between 0 and .99999. This number represents a time between 00:00:00 and 23:59:59, or 12:00:00 AM and 11:59:59 PM, inclusive. If strexpr contains invalid date information, TimeValue returns an error. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example prints the time string "2:30:25 PM" as "14:30:25": tval = TimeValue("2:30:25 PM")print(Hour(tval) ,":", Minute(tval), ":", Second(tval)) |
TLookup Function
Description | Look for specific data in a field in the source file, and replace that data in the target file with values from a multi-column external table. |
Return | Values from an external table based on a lookup key. |
Return (Null) | Error: Incorrect argument count |
Syntax | TLookup(value, table, sep, index[, default]) |
Parameters | This function has the following parameters: • value (required) - The lookup key. If a string literal, enclose in quotation marks. Use the FieldAt Function to specify a field value. • table (required) - The lookup table file name (including the full path). If a string literal, enclose in quotation marks.. • sep (required) - The character which separates the two columns of data, such as a comma or pipe. This must be a unique character that does not exist in the data. If a string literal, enclose in quotation marks. • index (required) - Used to specify the ordinal index of the lookup value in the list of values following the key. It is a zero based index. • default (optional) - If the default value is not provided and if the input value or field is not found in the lookup table, then the expression returns an empty string. If the default value is provided and if the input value or field is not found on the lookup table, then the string specified as the default is returned. |
Remarks | Tip... The following section gives details on manually creating lookup files that can be accessed using the TLookup function. To manually create lookup files: Create and save a lookup file in a simple Text Editor. The file must contain at least two columns of data. Each line in the lookup file must contain a lookup key in one column, a separator (such as a comma or a pipe), and a corresponding value in the second column. Limitations of lookup files: Maximum line length is 4096 characters; maximum key length is 31 bytes (the field can be longer, but only the first 31 characters are used for indexing). Because of key length limitation, only first 31 characters are compared during search, irrespective of field length. Time of execution will increase with the size of the lookup file. Also, a smaller key is significantly faster. The LookupIndexDir file setting is used as the directory to store lookup function index files. If not set, the default is still the InstallDir/lookup directory. Modify this file setting in the UserInfo section of the cosmos.ini file. If the index directory cannot be created, or is not accessible as read/write, and the size of the lookup file is more than 32000 bytes then an error occurs. If the size is less than 32000 the lookup will continue to process without an index. Note: The use of duplicate keys is not permitted because they can result in index corruption that causes the lookup to behave incorrectly. |
See Also | |
Example | In this example, the lookup file contains the following data: Smith|11|22|33|44 Jones|aa|bb|cc|dd First column is the lookup key and second column contains corresponding value to the lookup key with separator. Example 1: The following expression returns 33: TLookup("Smith","lookup.txt","|",3) Smith is the lookup key present in the lookup file table. The lookup.txt is the file that contains lookup data (complete file path required) where "|" separates the list of corresponding value to the key and ordinal index is 3. Hence, it returns the 3rd value from the corresponding value list for the lookup key "Smith". Example 2: The following expression returns aa: TLookup("Jones","lookup.txt","|",1) Jones is the lookup key present in the lookup file table. The lookup.txt is the file that contains lookup data (complete file path required) where "|" separates the list of corresponding value to the key and ordinal index is 1. Hence, it returns the 1st value from the corresponding value list for the lookup key "Jones". |
Transliterate Function
Description | Translate characters between two character sets |
Return | Characters that meet the match criteria |
Return (Null) | Error: Incorrect argument count |
Syntax | Transliterate(source_char_set,target_char_set,string) |
Parameters | This function has the following parameters: • source_char_set - Defines the set of characters for the source alphabet. Any character text string. If a string literal, enclose in quotation marks. • target_char_set - Defines the set of characters for the target alphabet. Any character text string. If a string literal, enclose in quotation marks. • string - The character text string to be transliterated. If a string literal, enclose in quotation marks. |
Remarks | Transliterate converts characters in the third parameter string which are members of the source alphabet to the corresponding characters in the target alphabet. If the source alphabet is larger than the target alphabet, the characters in the source alphabet without corresponding elements in the target alphabet is mapped to empty strings. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following examples take the source and target character strings and return a transliterated string: Transliterate("abc", "ABC", "abcdefg") 'returns "ABCdefg" Transliterate("abcdefg", "ABC", "abcdefg") 'returns "ABC" Transliterate(" ", "", "The red fox") 'returns "Theredfox" Transliterate("!@#$%", "______", "The national characters !@#$%") 'returns "The national characters ____" |
Trim Function
Description | Remove leading and trailing space from a string. |
Return | Copy of a string with spaces removed from the beginning and end. (Spaces in the middle of data will not be removed.) |
Return (Null) | Error: Incorrect argument count |
Syntax | Trim(stringexpr) |
Parameters | This function has the following parameters: • stringexpr - Any expression containing numeric or text characters. If a string literal, enclose in quotation marks. |
Remarks | The Trim Function is often used in conjunction with other functions. Adding the Trim function is often the cure for otherwise nonworking functions (such as when spaces occur in source data). Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example removes the spaces from the string " Test String ": Trim(" Test String ") 'Returns "Test String" The following example removes the spaces from the data in FIELD1 so the remainder of the expression will function correctly: If Trim("/SOURCE/R1/FIELD1") <> "" Then DateValMask(FieldAt("/SOURCE/R1/FIELD1"), "yyyymmdd") |
UBound Function
Description | Find the index of the last element (upper bound) in an array or an array dimension |
Return | Upper bound of the specified array |
Return (Null) | Error: Invalid expression syntax |
Syntax | UBound(arrayName[,Dimension]) |
Parameters | This function has the following parameters: • arrayName (required) - The array variable name. • dimension (optional) - Whole number indicating which dimension's upper bound is returned. (Use 1 for the first dimension, 2 for the second, and 3 for the third. If dimension is omitted, 1 is assumed.) |
Remarks | Each element of an array stores one value and is referenced by using its index (coordinate position) after the array name. The index of the first element of an array is called its lower bound. (See LBound Function.) The index of the last element is called its upper bound. By default, an array is indexed beginning with zero. The upper and lower bounds of an array can be inspected using LBound and UBound. Using scalar or undeclared variables results in a runtime error. Always declare all variables before using UBound. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Dim MyArray(1 To 100, 0 To 10, -5 To 5) UBound(MyArray) 'returns 100 UBound(MyArray, 1) 'returns 100 UBound(MyArray, 2) 'returns 10 UBound(MyArray, 3) 'returns 5 |
UCase Function
Description | Transform all characters in a string to uppercase. |
Return | String with all uppercase characters |
Return (Null) | Error: Incorrect argument count |
Syntax | UCase(strexpr) |
Parameters | This function has the following parameters: • strexpr - Any string expression. If a string literal, enclose in quotation marks. |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following expression returns the string "REMAINS OF THE DAY": UCase("Remains of the Day") This expression causes the string data in "Field1" of the source data file to be returned as uppercase in the target file. UCase(FieldAt("/SOURCE/R1/Field1")) The expression below cleans data entered by various employees in the source field ("FOOD TYPE"). The data includes many forms of the key word "TEX" in all lower case, all upper case, mixed upper-lower, and a combination of each. This expression changes the data to all uppercase, extracts "TEX", and changes "TEX" to "TEX MEX". Any other data other than "TEX" in the ("FOOD TYPE") field is converted to the target in its original form, such as THAI or ITALIAN. If UCase(Left(Trim(FieldAt("/SOURCE/R1/FOOD TYPE") ), 3)) = "TEX" Then "TEX MEX" Else UCase(Parse(1,Trim(FieldAt("/SOURCE/R1/FOOD TYPE")), " ")) |
UnString Function
Description | Splits a string using one or more separator characters and returns a substring corresponding to an index. Note: The Parse and UnString functions are interchangeable and use the same syntax. |
Return | Parse "parts" to a different field or fields. |
Return (Null) | Error: Too few arguments for function |
Syntax | Unstring(number, string, character) |
Parameters | This function has the following parameters: • number - Indicates which "part" or section of the parsed data you want to return. "1" indicates the first part, "2" indicates the second part, "3" indicates the third part, and so on. • string - Indicates the string expression to parse. If a string literal, then enclose in quotation marks. • character - Indicates the character to parse. If a string literal, then enclose in quotation marks. |
Remarks | The Unstring function enables the user to parse the data in one field in the source file and write the "parts" of the data to a field or fields in the target file. Unstring is performed on a unique character, such as a space, an asterisk, a dash, and so on. It is important to understand that if you parse a character that is part of the actual data, undesirable results may occur. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example parses a field in the source file called "FIELD1" on a space, and writes each of the three "parts" of the parsed data into three fields in the target file: Unstring(1, FieldAt("/SOURCE/R1/FIELD1"), " ") Unstring(2, FieldAt("/SOURCE/R1/FIELD1"), " ") Unstring(3, FieldAt("/SOURCE/R1/FIELD1"), " ") The Unstring function strips the word "Or" as provided in the following example. The source contains "Mr. John Q. Smith Or Mrs. John Q. Smith". The expression finds the "Or" within the data and concatenates the names with a space between them: Unstring (1, FieldAt("/SOURCE/R1/Field1"), "Or") & " " & Unstring (2, FieldAt("/SOURCE/R1/Field1"), "Or") You can also use hex values within a Unstring function expression. The following expression parses the Address field on any blank (hex value 20). The "Chr(&H20)" portion of the expression satisfies the character parameter for the Unstring function:Parse(1, FieldAt("/SOURCE/R1/Address") , Chr(&H20)) Unstring (1, FieldAt("/SOURCE/R1/Address") , Chr(&H20)) or UnString(1, FieldAt("/SOURCE/R1/Address") , Chr(&H20)) |
UTF8Decode Function
Description | Decode UTF-8 formatted strings to be interpreted as Unicode strings. |
Return | Unicode format of the UTF-8 source string |
Return (Null) | Error: Incorrect argument count |
Syntax | UTF8Decode(src) |
Parameters | This function has the following parameters: • src (required) - The UTF-8 source string to decode. You can specify a name of a field. If a string literal, enclose in quotation marks. |
Remarks | UTF-8 is the Unicode transformation format that serializes a Unicode scalar value as a sequence of one to four bytes. In UTF-8, Unicode characters that correspond to the ASCII character set are represented as the ASCII characters. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following expression decodes the contents of "Field1": UTF8Decode(FieldAt("/SOURCE/R1/Field1")) |
UTF8Encode Function
Description | Encode Unicode strings to the UTF-8 format |
Return | UTF-8 format of the Unicode source string |
Return (Null) | Error: Incorrect argument count |
Syntax | UTF8Encode(src) |
Parameters | This function has the following parameters: • src (required) - The Unicode source string to encode. If a string literal, enclose in quotation marks. |
Remarks | UTF-8 is the Unicode transformation format that serializes a Unicode scalar value as a sequence of one to four bytes. In UTF-8, Unicode characters that correspond to the ASCII character set are represented as the ASCII characters. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following expression encodes the contents of "Field1" into UTF-8 format: UTF8Encode(FieldAt("/SOURCE/R1/Field1")) |
UUIDGenerate Function
Description | Allows you to generate Universally Unique IDentifiers (UUID). |
Return | An ID number that is universally unique. |
Return (Null) | |
Syntax | UUIDGenerate([digest], [useMD5]) |
Parameters | This function has the following parameters: • digest - (optional) If this parameter is a valid UUID (hex digits in the standard 8-4-4-4-12 format), the same UUID will be generated. • useMD5 - (Optional) If the digest parameter is not a valid UUID, the UUID will be generated from a SHA-1 or MD5 hash of the digest parameter. If the useMD5 parameter is true (actually, not false), then the MD5 hash will be generated. If the parameter is false, or not provided, then the SHA-1 hash will be generated. Note: If neither parameter is provided, or the digest parameter is an empty string, a random UUID will be generated. |
Remarks | Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following example generates a random UUID: UUIDGenerate() The following example generates “d0be2dc4-21be-4fcd-0172-e5afceea3970”, the SHA-1 hash of "apple": UUIDGenerate("apple") The following example generates “1f3870be-274f-6c49-b3e3-1a0c6728957f”, the MD5 hash of "apple": UUIDGenerate("apple", true) |
Val Function
Description | Strip non-numeric characters, blanks, tabs, and line feeds from a string |
Return | Numeric value of a string of characters |
Return (Null) | Error: Incorrect argument count |
Syntax | Val(string) |
Parameters | This function has the following parameters: • string (required) - A sequence of characters that can be interpreted as a numeric value. If a string literal, enclose in quotation marks. |
Remarks | This function stops reading the string at the first character (if it does not recognize it as a number). Val also strips blanks, tabs, and line feeds from string. Symbols and characters, such as dollar signs, commas, radix prefixes, octal or hexadecimals are not recognized by Val as numeric. Microsoft hexadecimal strings (i.e., Val("&H1A2B") are recognized by Val as numeric. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This expression returns 25 as a numeric value: Val("25") The following expression returns a numeric value for the data in "FIELD1", which is a "Text" data type in the source data file: Val(FieldAt("/SOURCE/R1/FIELD1")) |
ValMask Function
Description | Strip non-digit characters from a formatted string |
Return | Numeric value from a formatted string (resulting string is converted to a number, and that is the return value) |
Return (Null) | Error: Incorrect argument count |
Syntax | ValMask(string, list) |
Parameters | This function has the following parameters: • string (required) - Any valid string expression that can be interpreted as a numeric value. String may be entered as a valid field name. If a string literal, enclose in quotation marks. • list (required) - A list of non-digit characters. The supported characters are ‘,’, ‘%’, ‘$’, and ‘e’ (or ‘E’). |
Remarks | ValMask strips out non-digit characters and converts the result to a valid numeric value. Characters that ValMask will strip include: $ , % e Note: If the non-digit characters are in double-digit, then the function is not processed. For example, a single ‘-’ is allowed, but a second ‘-’ is not allowed. Therefore, for each character, if it is a digit, then save it. If it is one of the allowed characters, save it. Otherwise, stop processing. For example, if string is "-99" in =ValMask("-99","##"), then the first Minus sign is allowed and it will not be stripped. The returned value is -99. If string is "-99-99" in =ValMask("-99-99","####"), the second minus sign is not allowed and it is stripped. The returned value is -99. TestMask is similar to ValMask, but returns True if the string matches the mask and False if the string does not match exactly. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | The following expression returns a numeric value for the data in ("FIELD1"), which is a "Text" data type, but contains numbers such as 123.50 in the source data file. It changes $2,600 to 2600: ValMask("$2,600", "$#,###") This example performs many tasks on the source field ("Field1"). The StrReplace function strips out commas, moves negative signs to the front of strings, and removes decimal points and commas. Next, the ValMask function converts the text strings to numeric strings: a = StrReplace(",", "", StrReplace(".", "", FieldAt("/SOURCE/R1/Field1") )) IIf (Right(FieldAt("/SOURCE/R1/Field1") ,1) == "-", (ValMask(FieldAt("/SOURCE/R1/Field1"),"###############")*-1),Val( a )) |
Weekday Function
Description | Return an integer that represents the day of the week for a specified date string |
Return | Integer between 1 and 7 |
Return (Null) | Error: Incorrect argument count |
Syntax | Weekday(Data string) |
Parameters | This function has the following parameters: • data string- Any date string or numeric expression that can represent a date and/or time from January 1, 100 through December 31, 9999, where January 1, 1900 is 2. |
Remarks | Numbers to the left of the decimal point in number represent the date; numbers to the right of the decimal point represent the time. Negative numbers represent dates prior to December 30, 1899. If number is null, Weekday returns a null. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. The following are the days of the week and the corresponding integer that is returned with this function: Weekday - Integer Sunday - 1 Monday - 2 Tuesday - 3 Wednesday - 4 Thursday - 5 Friday - 6 Saturday - 7 |
See Also | |
Example | This expression returns an integer relating to the current system date. For example, Field1 contains the date 12/01/2003. This date was a Monday, so a "2" will returned to the target: Weekday(FieldAt("/SOURCE/R1/Field1")) In this example, a date is assigned as "MyAnniversary". A "4" is returned to the target, since November 13, 2002, was a Wednesday: MyAnniversary = "November 13, 2002" Weekday(MyAnniversary) |
Xlate Function
Description | Look for specific data in a field in the source file, and replace that data in the target file with values from an external table. |
Return | Values, including strings, hex values, and decimal values from a two column table |
Return (Null) | Error: Incorrect argument count |
Syntax | Xlate("string", "file") |
Parameters | This function has the following parameters: • string - The string that must be searched. If a string literal, enclose in quotation marks. See the table below for details on how to enter the three different types of strings. • file - The lookup table file name (including drive and path). If a string literal, enclose in quotation marks. |
Remarks | Xlate allows "lookups" on decimal values (0-255), strings (single characters), and hex values. Decimal values are the recommended table element. To use the Xlate function, create and save a lookup table in a simple Text Editor. The table must contain two columns of data. Each line in the lookup table must contain a lookup key in one column, a separator (must be a space or a tab), and a corresponding value in the second column. When you create the lookup table, you must enter the elements of the table as follows: • Decimal values: Enter as a value between 0 and 255 (such as 112 or 080). • Strings: Enter the literal string enclosed in single quotes (such as 'p' or 'D'). • Hex values: Enter a 0, an x, and then the 2-digit hex value (such as 0x70). |
See Also | |
Example | In this example, the map looks for any key in the lookup table file "filename.txt". If any key is found in "Field1" of the source file, then it will write the corresponding value(s) in the current field for every record in the target file: Xlate(FieldAt("/SOURCE/R1/Field1"),"filename.txt") If filename.txt contains following lookup table:  1. If field1 contains ‘-’, then it will be replaced by corresponding value ‘$’ as 0x2d is hexadecimal value of hyphen 2. If field1 contains ‘A’, then it will be replaced by corresponding value ‘1’ 3. If field1 contains ‘?’, then it will be replaced by corresponding value ‘,’ as 63 is decimal value of ’?’ |
XmlCData Function
Description | This function takes a value and wraps it in CDATA markers. The return string is used in an unquoted field. |
Syntax | XmlCData(value, defvalue, nullvalue) |
Parameters | This function has the following parameters: • value • defvalue • nullvalue If the value is empty and defvalue (default value) is provided, then the defvalue will be used. If both values are empty, then the value in the CDATA section will be the value of the nullvalue parameter if provided, otherwise "null". |
Examples | XmlCData("a value") will return "<![CDATA[a value]]>" XmlCData("", "default value") will return "<![CDATA[default value]]>" XmlCData("", "") will return "<!<CDATA[null]]>" XmlCData("", "", "") will return "<!<CDATA[]]>" |
Year Function
Description | Extract the year numeric value from any numeric string that represents a date |
Return | Integer between 100 and 9999, inclusive, which represents the year of a date argument |
Return (Null) | Error: Incorrect argument count |
Syntax | Year(dateString) |
Parameters | This function has the following parameters: • dateString - Any value or expression that can represent a date. |
Remarks | Numbers to the left of the decimal point in number represent the date; numbers to the right of the decimal point represent the time. Negative numbers represent dates prior to December 30, 1899. If number is null, Year returns a null. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | This example returns the current system year plus 30 years: Year(FieldAt("/SOURCE/R1/DATE")+30) This example returns the year portion from dates in the source ("ShipDate"). The DateValue function defines each date as date values, while the Year function defines those values as years: a = DateValue(Trim(FieldAt("/SOURCE/R1/ShipDate"))) Year(a) |
Last modified date: 10/22/2024