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") ) |
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() |
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 | To insert a null value in a field, enter the following expression: Null |
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. |
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)) |
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") |
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) |
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) |
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 |
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() |
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. |
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") |
Description | Reads and returns the rightmost characters of a string Note: The Right and Right$ functions are interchangeable. |
Return | Rightmost characters of a string |
Return (Null) | Error: Incorrect argument count |
Syntax | Right[$](strexpr,n) |
Parameters | This function has the following parameters: • strexpr (required) - Any string expression. If a string literal, enclose in quotation marks. • n (required) - A Long expression indicating how many characters to return. It must be between 0 and approximately 65,500, inclusive. If n is 0, the return value is a zero-length string. If n is greater than or equal to the number of characters in strexpr, the entire string is returned. |
Remarks | Right[$] returns a String. To find the number of characters in strexpr, use Len(strexpr). 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 three rightmost characters of the source field "Field1": Right(FieldAt("/SOURCE/R1/Field1"), 3) |
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) |
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())) |
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 or 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. See Option Math Precise Statement and Option Math Strict Statement. 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) |
Description | Trims trailing spaces from a string Note: The RTrim and RTrim$ functions are interchangeable. |
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" |
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(number) |
Parameters | This function has the following parameters: • number - Any 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 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) |
Description | Write serial values. 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. |
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) |
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) |
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) |
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") |
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 aborted. – 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 abort 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) |
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) |
Description | Returns a string consisting of a specified number of spaces. Note: The Space and Space$ functions are interchangeable. |
Return | White 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" |
Description | Parse a string |
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(10) MyArray = Split(FieldAt("/SOURCE/R1/Field1"), " ") |
Description | Convert a numeric value to a text string Note: The Str and Str$ functions are interchangeable. |
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 | This example returns the number 459 as a string representation. The Hex Browser interprets the string 459 as "20 34 35 39". The "20" represents the leading space reserved for the sign of the number: Str(459) |
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). |
Description | Returns a string consisting of a specified number of occurrences of a specified character. Note: The String and String$ functions are interchangeable. |
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, "*") |
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")))))) |
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") |
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")) |
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") |
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 Abort Function instead. |
See Also | |
Example | The following function can be written in an AfterEveryRecord 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 |
Description | Compares a string to a mask |
Return | Boolean value |
Return (Null) | Error: Incorrect argument count |
Syntax | TestMask(string,mask) |
Parameters | This function has the following parameters: • string - Any valid string expression that can be interpreted as a numeric value. If a string literal, enclose in quotation marks. • mask - Any valid string expression that represents display format characters. The display format characters describe how the expression argument is to be formatted. If a string literal, enclose in quotation marks. |
Remarks | This function compares a string to a mask, and if it matches exactly, it returns "-1" for a True result. Otherwise, it returns "0" for a False result. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. |
See Also | |
Example | Compare the string "$2,600" with the mask "$#,###": TestMask("$2,600", "$#,###") |
Description | Write current system time Note: The Time and Time$ functions are interchangeable. |
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() |
Description | Calculate time serial |
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) |
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)) |
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. • default (optional) - If no default is given, the expression returns an empty string if the input value or field is not found in the lookup table. If a default is given, the string specified as the default is returned if the input value or field is not found on the lookup table. • string - The string to search for. If a string literal, enclose in quotation marks. See the table below for details on how to enter the three different types of strings. • table - The lookup table file name (including drive and path). If a string literal, enclose in quotation marks. |
Remarks | To use the TLookup 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, and a corresponding value in the second column. |
See Also | |
Example | In this example, the lookup file contains the following data: Smith|11|22|33|44 Jones|aa|bb|cc|dd The following expression returns 33: TLookup("Smith","lookup.txt","|",3) The following expression returns aa: TLookup("Jones","lookup.txt","|",1) |
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 ____" |
Description | Remove leading and trailing space from a string Note: The Trim and Trim$ functions are interchangeable. |
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") |
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 |
Description | Transform all characters in a string to uppercase Note: The UCase and UCase$ functions are interchangeable. |
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")), " ")) |
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")) |
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")) |
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")8 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")) |
Description | Strip non-digit characters from a formatted string |
Return | Numeric value from a formatted string |
Return (Null) | Error: Incorrect argument count |
Syntax | ValMask(string, mask) |
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. • mask (required) - Any valid string expression that represents display format characters. The display format characters describe how the expression argument is to be formatted. If a string literal, enclose in quotation marks. |
Remarks | ValMask strips out non-digit characters and converts the result to a valid numeric value. Characters that ValMask will strip include: $, % e 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 )) |
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(number) |
Parameters | This function has the following parameters: • number - Any 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) |
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", "table") |
Parameters | This function has the following parameters: • string - The string to search for. If a string literal, enclose in quotation marks. See the table below for details on how to enter the three different types of strings. • table - 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", and for any found key 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") |
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(number) |
Parameters | This function has the following parameters: • number - Any 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, 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) |