User Guide > Scripting > Script Functions > Script Functions N-Z
Was this helpful?
Script Functions N-Z
This section 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
Causes a reread of a lookup file. Should be called after any change to the lookup file to enable lookup functions to use new values.
Return
Contents of the lookup file
Return (Null)
Error: Incorrect argument count
Syntax
ResetLookup(filename)
Parameters
This function has the following parameters:
filename (required) - Name of the lookup file to reread. If a string literal, enclose in quotation marks. 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 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)
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)
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 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)
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
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"
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"
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 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
TestMask Function
 
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", "$#,###")
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.
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
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
 
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"))
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: 06/13/2022