Functions Glossary
This chapter describes the rules syntax and the built-in functions available in Astera Data Integrator. The chapter includes:
Rules Syntax – describes the Rules Syntax in the application. For more information, click Rules Syntax.
Functions Reference – provides information about the functions available in the application. It also explains how to use these functions with the help of practical examples. For more information, click Functions Reference.
Rules Syntax
Rules Syntax
The syntax of the Astera Data Integrator rules language supports a wide variety of operators and has a large library of built-in functions. You can nest expressions and access the value of data elements throughout the application. You can also access the value of user input in the current data element. The rules in Astera Data Integrator support the following data types:
Data types and data type syntax
All operands have one of five data types. Astera's rules and expressions return values that are one of these five types, as described below:
Integer
An integer is a whole number without a fraction. The number 4 is an integer, while 4.5 is not.
Internally, an integer is represented as a 32-bit signed quantity. Allowed values range from 2+31 to 2-31.
Real
The rules engine uses a double precision floating point internally for real numbers with decimals. You must specify constants in digits with a decimal point.
Date
You must specify date constants in the [mm/dd/yyyy] format and enclose them in square brackets. Date is represented internally by a long integer. Accepted dates range from 01/01/1900 to 12/31/9999.
String
Strings represent a set of any arbitrary characters. You must surround string constants by double quotes, for example: ”r;string”.
Boolean
Boolean is an expression that evaluates to TRUE or FALSE. You can use Boolean data types as operands in rules only by creating an expression that returns a Boolean value.
Functions Reference
Functions Reference
A function is a statement that is executed when it is called from a point in the application. Its format is as follows:
Type name(param1, param2, ...) where:
Type is the data type of the data returned by the function.
Name is the identifier that is used to call the function.
Parameter(s) consist(s) of a data type followed by an identifier, for example - double number. Here double is the data type and number is the argument for the function. A comma separates each parameter.
The functions available in Astera can be grouped into seven categories. They are:
Logical
Date Time
Conversion
Math
String
General
Financial
Below is a complete listing of functions ordered alphabetically.
Abs(Decimal number)
Returns the absolute value for the given number.
Returns: Decimal
Parameters
Decimal number : Number for which the absolute value will be returned.
Abs(Real number)
Returns the absolute value for the given number.
Returns: Real
Parameters
Real number : Number for which the absolute value will be returned.
AddCDATAEnvelope(String str)
Surrounds the specified string with CDATA XML envelope. Use this function to surround a string when mapping as CDATA to XML destination.
Returns: String
Parameters
String str : String to surround with CDATA envelope.
AddDays(Date date, Integer days)
Adds number of days to the specified date.
Returns: Date
Parameters
Date date : Date to which days should be added
Integer days : Number of days to be added. To subtract, use negative days
AddHours(Date date, Integer hours)
Adds number of hours to the specified date.
Returns: Date
Parameters
Date date : Date to which hours should be added
Integer hours : Number of hours to be added. To subtract, use negative hours
AddMinutes(Date date, Integer minutes)
Adds number of miutes to the specified date.
Returns: Date
Parameters
Date date : Date to which minutes should be added
Integer minutes : Number of minutes to be added. To subtract, use negative minutes
AddMonths(Date date, Integer months)
Adds number of months to the specified date.
Returns: Date
Parameters
Date date : Date to which months should be added
Integer months : Number of months to be added. To subtract, use negative months
AddSeconds(Date date, Integer seconds)
Adds number of seconds to the specified date.
Returns: Date
Parameters
Date date : Date to which seconds should be added
Integer seconds : Number of seconds to be added. To subtract, use negative seconds
AddYears(Date date, Integer years)
Adds number of years to the specified date.
Returns: Date
Parameters
Date date : Date to which years should be added
Integer years : Number of years to be added. To subtract, use negative years
Age(Date start, Date end, Integer interval)
Computes difference between two dates and returns age in specified interval. Valid values for interval are: 1=Year, 2=Month, 3=Week, 4=Day, 5=Hour, 6=Minute, or 7=Second.
Returns: Integer
Parameters
Date start :
Date end :
Integer interval :
AppendTextToFile(String filePath, String text)
Appends string to a file. Creates a new file if does not exist.
Returns: Unknown
Parameters
String filePath : Full path of the file.
String text : Text to be appended to the file.
Asc(String str)
Returns ASCII value of the specified character
Returns: Integer
Parameters
String str :
Integer index :
Ceiling(Decimal number)
Returns the smallest integer greater than or equal to the number specified
Returns: Decimal
Parameters
Decimal number : Number for which the ceiling value will be returned.
Ceiling(Real number)
Returns the smallest integer greater than or equal to the number specified
Returns: Real
Parameters
Real number : Number for which the ceiling value will be returned.
Center(String str, Integer length)
Gets length of the specified string.
Returns: String
Parameters
String str : String which will be centered.
Integer length : Length of the output string.
CharToSeconds(String timestring)
Parses a date time string and returns number of seconds since the beginning of the day.
Returns: Integer
Parameters
String timestring :
Choose(Integer driver, Any default, Unknown caseValues)
Uses the value of first parameter to return the value of a case value parameter. If the driver value is zero, value of the first case value parameter is returned. For driver value of one, second case value parameter is returned and so on. If value there is no matching choose value to return, default parameter is returned.
Returns: Any
Parameters
Integer driver : An integer value that indicates which case value to return.
Any default : If no matching driver value is found, Choose returns this value.
Unknown caseValues : A series of case values.
Chr(Integer asciiValue)
Returns a single-character string that contains the character representing the specified ASCII value
Returns: String
Parameters
Integer asciiValue :
Coalesce(Any value1, Any value2)
Returns the first non-null value from specified parameters. Returns null if both parameters are null.
Returns: Any
Parameters
Any value1 : First expression
Any value2 : Second expression.
Coalesce(Unknown values)
Returns the first non-null value from specified parameters. Returns null if all parameters are null.
Returns: Any
Parameters
Unknown values : Values to coalesce
Compact(String str)
Removes all leading and trailing white space characters from the string as well as removes any duplicate white space characters within the string. White space characters include space, tab, return, and linefeed characters.
Returns: String
Parameters
String str : String that will be compacted
ComputeHash(String str, String key)
Returns a hashed representation of a given string using a key
Returns: String
Parameters
String str : The raw string to use as the input
String key : The secret key used to compute the hash
ComputeMD5(String str)
Computes the MD5 hash of a text value
Returns: String
Parameters
String str : The raw string to hash
Concatenate(String str1, String str2)
Cocatenates two strings and returns the result.
Returns: String
Parameters
String str1 : First string
String str2 :
Concatenate(Unknown strArray)
Cocatenates two strings and returns the result.
Returns: String
Parameters
Unknown strArray :
Contains(String lookFor, String lookIn)
Determines whether lookFor string exist in lookIn string.
Returns: Boolean
Parameters
String lookFor : String to search for.
String lookIn : String to search in.
ContainsAnyChar(String charsToLookFor, String lookIn)
Determines whether any character in charsToLookFor exist in lookIn string.
Returns: Boolean
Parameters
String charsToLookFor : String to search for.
String lookIn : String to search in.
ConvertEncoding(String str, String fromCodePage, String toCodePage)
Returns a string in the given encoding
Returns: String
Parameters
String str : The string to convert
String fromCodePage : The code page the string is currently encoded in. Default is UTF-8
String toCodePage : The code page to convert to. Default is UTF-8
CopyFile(String sourceFilePath, String destFilePath, Boolean overWrite)
Copies a file to the specified destination file.
Returns: Unknown
Parameters
String sourceFilePath : Full path of the source file.
String destFilePath : Full path of the destination file.
Boolean overWrite : Indicates whether the existing file should be overwritten.
CreateDateTime(String filePath)
Returns create date time for a file
Returns: Date
Parameters
String filePath : Path of the file.
Ctod(String dateString)
Parses a date string and reformats it to YYYYMMDD format.
Returns: String
Parameters
String dateString :
DateDifferenceDays(Date leftDate, Date rightDate)
Subtracts a date time value from another date time value and returns result in days.
Returns: Real
Parameters
Date leftDate : Date from which the other date will be subtracted.
Date rightDate : Date which will be subtracted.
DateDifferenceHours(Date leftDate, Date rightDate)
Subtracts a date time value from another date time value and returns result in hours.
Returns: Real
Parameters
Date leftDate : Date from which the other date will be subtracted.
Date rightDate : Date which will be subtracted.
DateDifferenceMonths(Date leftDate, Date rightDate)
Subtracts a date from another date and returns result in months.
Returns: Real
Parameters
Date leftDate : Date from which the other date will be subtracted.
Date rightDate : Date which will be subtracted.
DateDifferenceYears(Date leftDate, Date rightDate)
Subtracts a date time value from another date time value and returns difference in years.
Returns: Real
Parameters
Date leftDate : Date from which the other date will be subtracted.
Date rightDate : Date which will be subtracted.
DatePart(Date dateTime)
Removes time part from the given date and returns the truncated date.
Returns: Date
Parameters
Date dateTime : date from which the time part will be stripped
DateToIntegerYYYYMMDD(Date dateTime)
Converts the given date time to an integer YYYYMMDD format.
Returns: Integer
Parameters
Date dateTime :
DateToJulian(Date dateTime, Integer length)
TBD
Returns: String
Parameters
Date dateTime :
Integer length :
Day(Date dateTime)
Gets the day part of the date
Returns: Integer
Parameters
Date dateTime : Date for which the day will be returned.
DayOfWeek(Date dateTime)
Gets day of week as an integer.
Returns: Integer
Parameters
Date dateTime : Date time value for which the day will be returned.
DayOfWeekStr(Date dateTime)
Gets the name of the day of week for the specified date.
Returns: String
Parameters
Date dateTime : Date for which the day will be returned.
DayOfYear(Date dateTime)
Gets day of the year.
Returns: Integer
Parameters
Date dateTime : Date for which the day will be returned.
Days(TimeSpan timeSpan)
Returns Days component of the specified timeSpan
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which the Days component will be returned.
DaysInMonth(Integer year, Integer month)
Returns number of days in the specified month.
Returns: Integer
Parameters
Integer year :
Integer month :
DDB(Real Cost, Real Salvage, Real Life, Real Period)
Returns the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.
Returns: Real
Parameters
Real Cost :
Real Salvage :
Real Life :
Real Period :
DeleteFile(String filePath)
Deletes a file if exists.
Returns: Unknown
Parameters
String filePath : Full path of the file.
DirectoryExists(String filePath)
Checks whether a directory exists
Returns: Boolean
Parameters
String filePath : Path of the directory to check.
DoubleMetaphone(String str)
Returns Double Metaphone value for the given string.
Returns: String
Parameters
String str : String for which Double Metaphone is returned.
EndsWith(String str, String value)
Determines whether a string ends with a given value.
Returns: Boolean
Parameters
String str : String that will be search
String value : Value that will be searched
Exists(String lookFor, String lookIn)
Searches for lookFor string in a comma separated string, lookIn. If lookFor is found, in the list, returns true. Otherwise, returns false.
Returns: Boolean
Parameters
String lookFor : String to look for
String lookIn : A comma separated list of items that should be searched
Extract(String str, String start)
Returns a string enveloped between’start’ string and ‘end’ string. If ‘end’ is ommitted, string starting after ‘start’ is returned.
Returns: String
Parameters
String str : The string to search in
String start : The beginning part of the string to search for
String end : The optional ending of the string to search for
Boolean isCaseSensitive : True to match
FileExists(String filePath)
Checks whether a file exists
Returns: Boolean
Parameters
String filePath : Path of the file to check.
FileLength(String filePath)
Returns number of bytes in a file
Returns: Integer
Parameters
String filePath : Path of the file.
FileLineCount(String filePath)
Returns number of lines in a file
Returns: Integer
Parameters
String filePath : Path of the file.
Find(String lookFor, String lookIn)
Searches the string specified in lookFor in the lookIn string.
Returns: Integer
Parameters
String lookFor : String to search for.
String lookIn : String to search in.
Boolean isCaseSensitive :
Find(String lookFor, String lookIn, Integer startAt)
Searches an integer representing the starting position of lookFor, in lookIn, starting at startAt.
Returns: Integer
Parameters
String lookFor : String to search for.
String lookIn : String to search in.
Integer startAt : Position to start searching at. Zero indicates the first position.
Boolean isCaseSensitive :
FindLast(String lookFor, String lookIn)
Searches the lookIn string for the last occurrence of the string specified.
Returns: Integer
Parameters
String lookFor : String to search for.
String lookIn : String to search in.
Boolean isCaseSensitive :
Floor(Decimal number)
Rounds the largest integer equal to or less than the number specified
Returns: Decimal
Parameters
Decimal number : Number for which the floor value will be returned.
Floor(Real number)
Rounds the largest integer equal to or less than the number specified
Returns: Real
Parameters
Real number : Number for which the floor value will be returned.
FromBytes(ByteArray bytes, String codePage)
Returns a string by reading a byte array in a specified encoding
Returns: String
Parameters
ByteArray bytes : The raw byte array containing a textual value
String codePage : The code page the byte array is currently encoded in. Default is UTF-8
FromJulianDate(Real julianDate)
Converts a Julian date value to a date time value.
Returns: Date
Parameters
Real julianDate :
FromJulianDayNumber(Integer julianDayNumber)
Converts a Julian Day Number to a Date.
Returns: Date
Parameters
Integer julianDayNumber :
FromTicksLocal(Integer ticks)
Gets local date time for the specified tick values.
Returns: Date
Parameters
Integer ticks : Local ticks for which the date time will be returned.
FromTicksUtc(Integer ticks)
Gets UTC date time for the specified tick values.
Returns: Date
Parameters
Integer ticks : UTC Ticks for which date time will be returned.
FromUnixEpoc(Integer unixEpoc)
Get date time object for the specified Unix Epoc value.
Returns: Date
Parameters
Integer unixEpoc :
FV(Real Rate, Real NPer, Real Pmt, Real PV)
Returns the future value of an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real Rate :
Real NPer :
Real Pmt :
Real PV :
GetDateComponents(Date dateTime)
Gets a structure representing individual date components.
Returns: Unknown
Parameters
Date dateTime : Date time for which components are required
GetDigits(String str)
Removes all occurrences of non-numeric characters and returns all digits in str.
Returns: String
Parameters
String str : String to remove non-numeric characters from.
GetDirectory(String filePath)
Extracts directory path from the given file path
Returns: String
Parameters
String filePath : Full path of the file.
GetExcelWorksheets(String excelFilePath)
Enumerates Worksheets from an Excel file.
Returns: Unknown
Parameters
String excelFilePath : Full path of the Excel workbook.
GetFileExtension(String filePath)
Extracts file extension from the given file path
Returns: String
Parameters
String filePath : Full path of the file.
GetFileInfo(String filePath)
Returns information about a file
Returns: Unknown
Parameters
String filePath : Path of the file.
GetFileName(String filePath)
Extracts file name from the given file path
Returns: String
Parameters
String filePath : Full path of the file.
GetFileNameWithoutExtension(String filePath)
Extracts file name without extension from the given file path
Returns: String
Parameters
String filePath : Full path of the file.
GetFindCount(String lookFor, String lookIn)
Searches the string specified in lookFor in the lookIn string.
Returns: Integer
Parameters
String lookFor : String to search for.
String lookIn : String to search in.
Boolean isCaseSensitive :
GetLineFeed()
Gets the line feed character.
Returns: String
Parameters
GetMatchRegEx(String pattern, String str)
Scans the string for an occurrence of text matching the specified regular expression.
Returns: String
Parameters
String pattern : Regular expression pattern to match
String str : String to search for a match
GetMatchRegEx(String pattern, String str, Integer startAt)
Scans the string, starting at the specified position, for an occurrence of text that matches the specified regular expression and returns the result.
Returns: String
Parameters
String pattern : Regular expression pattern to match
String str : String to search for a match
Integer startAt : The character position at which to start the search
GetToken(String str, String delimiter, Integer tokenIndex)
Retrieves the specified token in a string delimited by ‘delimiter’.
Returns: String
Parameters
String str :
String delimiter :
Integer tokenIndex :
HexToInteger(String value)
Converts a hex value into integer.
Returns: Integer
Parameters
String value : Value to be converted
Hour(Date dateTime)
Gets the hour part of the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which the hour will be returned.
Hours(TimeSpan timeSpan)
Returns Hours component of the specified timeSpan
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which the Hours component will be returned.
If(Boolean condition, Boolean thenValue, Boolean elseValue)
Evaluates condition and if true, returns the result of thenExpr, else returns the result of elseExpr.
Returns: Boolean
Parameters
Boolean condition :
Boolean thenValue : The value of this parameter is returned if the result of ‘condition’ parameter is true.
Boolean elseValue : The value of this parameter is returned if the result of ‘condition’ parameter is false.
If(Boolean condition, Date thenValue, Date elseValue)
Evaluates condition and if true, returns the result of thenExpr, else returns the result of elseExpr.
Returns: Date
Parameters
Boolean condition :
Date thenValue : The value of this parameter is returned if the result of ‘condition’ parameter is true.
Date elseValue : The value of this parameter is returned if the result of ‘condition’ parameter is false.
If(Boolean condition, Integer thenValue, Integer elseValue)
Evaluates condition and if true, returns the result of thenExpr, else returns the result of elseExpr.
Returns: Integer
Parameters
Boolean condition : Condition that is evaluated.
Integer thenValue : The value of this parameter is returned if the result of ‘condition’ parameter is true.
Integer elseValue : The value of this parameter is returned if the result of ‘condition’ parameter is false.
If(Boolean condition, Real thenValue, Real elseValue)
Evaluates condition and if true, returns the result of thenExpr, else returns the result of elseExpr.
Returns: Real
Parameters
Boolean condition :
Real thenValue : The value of this parameter is returned if the result of ‘condition’ parameter is true.
Real elseValue : The value of this parameter is returned if the result of ‘condition’ parameter is false.
If(Boolean condition, String thenValue, String elseValue)
Evaluates condition and if true, returns the result of thenExpr, else returns the result of elseExpr.
Returns: String
Parameters
Boolean condition :
String thenValue : The value of this parameter is returned if the result of ‘condition’ parameter is true.
String elseValue : The value of this parameter is returned if the result of ‘condition’ parameter is false.
Insert(String insertInto, String insertString, Integer position)
Inserts a string at a specified position within the string. If the position is >= string length, string is appended.
Returns: String
Parameters
String insertInto : String in which the other string will be inserted.
String insertString : String which will be inserted.
Integer position : Position at which the string will be inserted.
IPmt(Real rate, Real periods, Real numPeriods, Real PV)
Returns the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
IPmt(Real rate, Real periods, Real numPeriods, Real PV, Real FV)
Returns the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
Real FV :
IsAlpha(String str)
Checks whether specified character is alphabetic.
Returns: Boolean
Parameters
String str : String containing the character to test
Integer index : Numerical position of the character in the string to test. If not given, index at zero is presumed.
IsBlank(String str)
Checks whether specified character is an white space.
Returns: Boolean
Parameters
String str : String containing the character to test
Integer index : Numerical position of the character in the string to test. If not given, index at zero is presumed.
IsDate(Any value)
Checks whether a given value represents valid date.
Returns: Boolean
Parameters
Any value : Value to be checked
IsDate(String str, String format)
Checks whether a given value represents valid date in the given format.
Returns: Boolean
Parameters
String str : Value to be checked
String format : Format for parsing the string. Any valid .Net date format.
IsInteger(Any value)
Checks whether a given value represents an integer.
Returns: Boolean
Parameters
Any value : Value to be checked
IsLower(String str)
Checks whether specified character is lowercase alphabetic.
Returns: Boolean
Parameters
String str : String containing the character to test
Integer index : Numerical position of the character in the string to test. If not given, index at zero is presumed.
IsMatchRegEx(String pattern, String str)
Indicates whether the regular expression finds a match in str.
Returns: Boolean
Parameters
String pattern : Regular expression pattern to match
String str : String to search for a match
IsMatchRegEx(String pattern, String str, Integer startAt)
Indicates whether the regular expression finds a match in the input string starting at the specified position.
Returns: Boolean
Parameters
String pattern : Regular expression pattern to match
String str : String to search for a match
Integer startAt : The character position at which to start the search
IsNotNull(Any val)
Determines whether a value is not null. This function accepts values of all types.
Returns: Boolean
Parameters
Any val : Expression that will be checked.
IsNull(Any val)
Determines whether a value is null. This function accepts values of all types
Returns: Boolean
Parameters
Any val : Expression that will be evaluated.
IsNullOrEmpty(String val)
Determines whether a string is null or zero length.
Returns: Boolean
Parameters
String val : expression that will be evaluated.
IsNullOrWhitespace(String val)
Determines whether a string is null, zero length or contains only white space characters. White space characters are space, tab, return, and linefeed.
Returns: Boolean
Parameters
String val : expression that will be evaluated.
IsNumeric(Any value)
Checks whether a given value represents an integer or a real number.
Returns: Boolean
Parameters
Any value : Value to be checked
IsProcessRunning(String processName)
Checks whether a process is currently running
Returns: Boolean
Parameters
String processName : Name of the process to check.
IsRealNumber(Any value)
Checks whether a given value represents a real number.
Returns: Boolean
Parameters
Any value : Value to be checked
IsUpper(String str)
Checks whether specified character is uppercase alphabetic.
Returns: Boolean
Parameters
String str : String containing the character to test
Integer index : Numerical position of the character in the string to test. If not given, index at zero is presumed.
IsUSPhone(String str)
Checks whether a given value represents valid United States phone number. This method tests format and digits to determine whether the given string is potentially a valid US phone number.
Returns: Boolean
Parameters
String str : Value to be checked
IsUSZipCode(String str)
Checks whether a given value represents valid United States Zip Code format. This method tests format and digits to determine whether the string represents valid US Zip code format.
Returns: Boolean
Parameters
String str : Value to be checked
IsValidName(String name)
Returns true if the specified string is a valid object or element name
Returns: Boolean
Parameters
String name :
IsValidSqlDate(Date date)
Checks if a date time value is a valid SQL Date time value.
Returns: Boolean
Parameters
Date date : DateTime value to be checked
JulianToDate(String julianDate)
TBD
Returns: Date
Parameters
String julianDate :
LastUpdateDateTime(String filePath)
Returns last update date time for a file
Returns: Date
Parameters
String filePath : Path of the file.
Left(String str, Integer noOfChars)
Gets the leftmost characters from the specified string.
Returns: String
Parameters
String str : String whose part will be returned
Integer noOfChars : Maximum number of characters to return.
LeftAlign(String str, Integer length)
Pads a string to the right.
Returns: String
Parameters
String str : String which will be left-aligned.
Integer length : Length of the output string.
LeftOf(String str, String lookFor)
Searches str for lookFor and, if found, returns all characters before it.
Returns: String
Parameters
String str : String that will be searched and whose part will be returned
String lookFor :
Boolean isCaseSensitive :
Length(String str)
Gets the number of characters in the specified string.
Returns: Integer
Parameters
String str : String whose length will be returned.
LSplit(String str, Integer maxTokens, String delimiter, Integer returnToken)
Splits a string into a maximum ‘maxTokens’ tokens delimited by ‘delimiter’. Returns token at index ‘returnToken’ counting from the left.
Returns: String
Parameters
String str : The string to be broken into tokens
Integer maxTokens : The maximum number of tokens to split the string into. -1 is taken to be unlimited. The number should be greater than the index of the token to be returned.
String delimiter :
Integer returnToken : The index (counting from left to right) of the token generating by splitting the string
Max(Date first, Date second)
Returns maximum of two date values
Returns: Date
Parameters
Date first : First value
Date second : Second value
Max(Decimal first, Decimal second)
Returns maximum of two decimal numbers
Returns: Decimal
Parameters
Decimal first : First value
Decimal second : Second value
Max(Integer first, Integer second)
Returns maximum of two integer
Returns: Integer
Parameters
Integer first : First value
Integer second : Second value
Max(Real first, Real second)
Returns maximum of two real numbers
Returns: Real
Parameters
Real first : First value
Real second : Second value
Mid(String str, Integer startAt)
Gets all characters starting with startAt position.
Returns: String
Parameters
String str : String whose part will be returned
Integer startAt : Start position of the string to return.
Mid(String str, Integer startAt, Integer noOfChars)
Gets part of the string starting at startAt.
Returns: String
Parameters
String str : String whose part will be returned
Integer startAt : Start position of the string to return.
Integer noOfChars : Maximum number of characters to return.
Milliseconds(TimeSpan timeSpan)
Returns Milliseconds component of the specified timeSpan
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which the Milliseconds component will be returned.
Min(Date first, Date second)
Returns minimum of two date values
Returns: Date
Parameters
Date first : First value
Date second : Second value
Min(Decimal first, Decimal second)
Returns minimum of two decimal numbers
Returns: Decimal
Parameters
Decimal first : First value
Decimal second : Second value
Min(Integer first, Integer second)
Returns minimum of two integer
Returns: Integer
Parameters
Integer first : First value
Integer second : Second value
Min(Real first, Real second)
Returns minimum of two real numbers
Returns: Real
Parameters
Real first : First value
Real second : Second value
Minute(Date dateTime)
Gets the minute part of the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which the minute will be returned.
Minutes(TimeSpan timeSpan)
Returns Minutes component of the specified timeSpan
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which the Minutes component will be returned.
Month(Date dateTime)
Gets the month part of the date.
Returns: Integer
Parameters
Date dateTime : Date for which the month will be returned.
MoveFile(String filePath, String newFilePath)
Moves a file if exists.
Returns: Unknown
Parameters
String filePath : Full path of the file.
String newFilePath : New path of the file.
NewGuid()
Generates a new GUID.
Returns: Guid
Parameters
Now()
Gets the current system date and time.
Returns: Date
Parameters
NumPeriods(Real rate, Real Pmt, Real PV)
Returns the number of periods for an annuity based on periodic fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real Pmt :
Real PV :
NumPeriods(Real rate, Real Pmt, Real PV, Real FV)
Returns the number of periods for an annuity based on periodic fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real Pmt :
Real PV :
Real FV :
PadLeft(String str, String padChar, Integer length)
Pads a string to the left with the specified character.
Returns: String
Parameters
String str : String which will be padded to left.
String padChar : A character string whose first character will be used to pad the string.
Integer length : Length of the output string.
PadRight(String str, String padChar, Integer length)
Pads a string to the right with the specified character.
Returns: String
Parameters
String str : String which will be padded to right.
String padChar : A character string whose first character will be used to pad the string.
Integer length : Length of the output string.
ParseAddress(String address, Integer addressComponent)
Parses an address string and returns specified component. Valid values for addressComponent are: 1=Street, 2=City, 3=State, 4=Zip.
Returns: String
Parameters
String address :
Integer addressComponent :
ParseAddressUS(String fullAddress, String outputCase)
Parses an address string. This function parses United States addresses only.
Returns: Unknown
Parameters
String fullAddress : Address input structure
String outputCase :
ParseAddressUS(String street, String cityStateZip, String outputCase)
Parses an address string and returns its components. This function parses United States addresses only.
Returns: Unknown
Parameters
String street : Street Address
String cityStateZip : City, State Zip
String outputCase :
ParseAndCorrectAddressUS(Unknown input)
Parses an address and corrects address information where needed and returns its components. This function works with United States addresses only.
Returns: Unknown
Parameters
Unknown input : Input address and case option
ParseDate(Unknown formats, String str)
Parses a date based on formats specified. This function successively applies specified formats and returns date time value if date was parsed using any of the specified formats.
Returns: Date
Parameters
Unknown formats :
String str : String to be converted
ParseName(String name)
Parses a name into its components.
Returns: Unknown
Parameters
String name : Full name to be parsed
ParseName(String name, Integer nameComponent)
Parses a name and returns specified component indicated by ‘nameComponent’.
Returns: String
Parameters
String name :
Integer nameComponent : A number representing a specific token of a parsed name. Valid value for nameComponent are: 1=Prefix, 2=First, 3=Middle, 4=Last, 5=Suffix
ParseNameEx(Unknown input)
Parses a name, determines gender, and returns any nicknames.
Returns: Unknown
Parameters
Unknown input : Input address and case option
Pmt(Real rate, Real periods, Real numPeriods, Real PV)
Returns the payment for an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
Pmt(Real rate, Real periods, Real numPeriods, Real PV, Real FV)
Returns the payment for an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
Real FV :
Pow(Real base, Real exponent)
Returns base raised to power exponent.
Returns: Real
Parameters
Real base : Base value.
Real exponent : Exponent value.
PPmt(Real rate, Real periods, Real numPeriods, Real PV)
Returns the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
PPmt(Real rate, Real periods, Real numPeriods, Real PV, Real FV)
Returns the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
Returns: Real
Parameters
Real rate :
Real periods :
Real numPeriods :
Real PV :
Real FV :
Proper(String str)
Capitalizes first letter of each word and returns the resulting string.
Returns: String
Parameters
String str : String to convert
PushdownSql(String formatString, Unknown args)
Used to inject SQL expression in Pushdown mode
Returns: String
Parameters
String formatString : Format string with {0},{1},{2}… placeholders for arguments
Unknown args : Variable number of argumnents for substitution in formatString
PV(Real Rate, Real NPer, Real Pmt)
Returns the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.
Returns: Real
Parameters
Real Rate :
Real NPer :
Real Pmt :
PV(Real Rate, Real NPer, Real Pmt, Real FV)
Returns the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.
Returns: Real
Parameters
Real Rate :
Real NPer :
Real Pmt :
Real FV :
Quarter(Date dateTime)
Returns calendar quarter for the specified date time value.
Returns: Integer
Parameters
Date dateTime :
Rate(Real numPeriods, Real Pmt, Real PV)
Returns the the interest rate per period for an annuity.
Returns: Real
Parameters
Real numPeriods :
Real Pmt :
Real PV :
Rate(Real numPeriods, Real Pmt, Real PV, Real FV)
Returns the the interest rate per period for an annuity.
Returns: Real
Parameters
Real numPeriods :
Real Pmt :
Real PV :
Real FV :
ReadFileBytes(String filePath)
Reads all bytes of the file and returns a byte array containing all of file’s contents. Use this function only for small binary files.
Returns: ByteArray
Parameters
String filePath : Full path of the file.
ReadFileText(String filePath)
Reads entire text of the file and returns the text. Use this function only for very small files.
Returns: String
Parameters
String filePath : Full path of the file.
RefinedSoundex(String str)
Returns Refined Soundex value for the given string.
Returns: String
Parameters
String str : String for which Refined Soundex is returned.
RemoveAt(String str, Integer startAt, Integer noOfChars)
Removes numberOfCharacters characters starting with startPosition from the given string.
Returns: String
Parameters
String str : Input string
Integer startAt : Position of the first character to be removed.
Integer noOfChars : Number of characters to be removed.
RemoveChars(String str, Integer startPosition, Integer countToRemove)
Removes specified characters from the given string.
Returns: String
Parameters
String str : The string containing characters to remove.
Integer startPosition : Position in string from where to start removing characters
Integer countToRemove : Position in string that marks the end of the substring being removed
RemoveChars(String str, String charsToRemove)
Removes all occurrences of any character specified in charsToRemove parameter.
Returns: String
Parameters
String str : String to remove characters from
String charsToRemove : String containing characters to be removed. Any character found in this string will be removed from str.
Repeat(String str, Integer count)
Returns a string repeating the input string a ‘count’ number of times.
Returns: String
Parameters
String str : The string to repeat
Integer count : The number of times to repeat
String delimiter : Delimiter to add after each instance
ReplaceAll(String str, String lookFor, String replaceWith)
Replaces all occurrences of lookFor with replaceWith.
Returns: String
Parameters
String str : String in which the characters will be replaced.
String lookFor : The string that will be searched.
String replaceWith : String that will replace the searched screen.
ReplaceFirst(String str, String lookFor, String replaceWith)
Replaces first occurrence of lookFor with replaceWith.
Returns: String
Parameters
String str : String in which the characters will be replaced.
String lookFor : The string that will be searched.
String replaceWith : String that will replace the searched screen.
Boolean isCaseSensitive :
ReplaceLast(String str, String lookFor, String replaceWith)
Replaces the last occurrence of lookFor with replaceWith.
Returns: String
Parameters
String str : String in which the characters will be replaced.
String lookFor : The string that will be searched.
String replaceWith : String that will replace the searched screen.
Boolean isCaseSensitive :
ReplaceRegEx(String pattern, String searchIn, String replaceWith)
Scans the input string for the specified pattern and replaces it with the value specified in replaceWith parameter.
Returns: String
Parameters
String pattern : Regular expression pattern to match
String searchIn : String to search for a match
String replaceWith : Replacement string
ReplaceRegEx(String pattern, String searchIn, String replaceWith, Integer startAt)
Scans the input string for the specified pattern starting at the startAt position and replaces it with the value specified in replaceWith parameter.
Returns: String
Parameters
String pattern : Regular expression pattern to match
String searchIn : String to search for a match
String replaceWith : Replacement string
Integer startAt : The character position at which to start the search
Reverse(String str)
Reverses all characters in a string so that the original text is mirrored.
Returns: String
Parameters
String str : String to reverse
Right(String str, Integer noOfChars)
Gets rightmost noOfChars from the specified string.
Returns: String
Parameters
String str : String whose part will be returned
Integer noOfChars : Maximum number of characters to return.
RightAlign(String str, Integer length)
Pads a string to the left with spaces.
Returns: String
Parameters
String str : String which will be right-aligned.
Integer length : Length of the output string.
RightOf(String str, String lookFor)
Searches str for lookFor and, if found, returns all characters after it.
Returns: String
Parameters
String str : String that will be searched and whose part will be returned
String lookFor :
Boolean isCaseSensitive :
Round(Decimal number)
Rounds a given number.
Returns: Decimal
Parameters
Decimal number : Number for which the rounded value will be returned.
Round(Decimal number, Integer decimals)
Rounds a given number to the specified number of decimal places.
Returns: Decimal
Parameters
Decimal number : Number for which the rounded value will be returned.
Integer decimals :
Round(Real number)
Rounds a given number.
Returns: Real
Parameters
Real number : Number for which the rounded value will be returned.
Round(Real number, Integer decimals)
Rounds a given number to the specified number of decimal places.
Returns: Real
Parameters
Real number : Number for which the rounded value will be returned.
Integer decimals :
RSplit(String str, Integer maxTokens, String delimiter, Integer returnToken)
Splits a string into a maximum ‘maxTokens’ tokens delimited by ‘delimiter’. Returns token at index ‘returnToken’ counting from the right.
Returns: String
Parameters
String str : The string to be broken into tokens
Integer maxTokens : The maximum number of tokens to split the string into. -1 is taken to be unlimited. The number should be greater than the index of the token to be returned.
String delimiter :
Integer returnToken : The index (counting from right to left) of the token generating by splitting the string
Second(Date dateTime)
Gets the second part of the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which the second will be returned.
Seconds(TimeSpan timeSpan)
Returns Seconds component of the specified timeSpan
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which the Seconds component will be returned.
SecondsTochar(Integer secondsToday)
Computes time of day for secondsToday parameter and returns a string representing time of day.
Returns: String
Parameters
Integer secondsToday :
SLN(Real Cost, Real Salvage, Real Life)
Returns the straight-line depreciation of an asset for a single period.
Returns: Real
Parameters
Real Cost :
Real Salvage :
Real Life :
Soundex(String str)
Returns Soundex value for the given string.
Returns: String
Parameters
String str : String for which Soundex is returned.
SplitString(String str, String separator)
Splits a string into multiple parts along the separator string.
Returns: Unknown
Parameters
String str : String to split.
String separator : Separator for parsing the string.
SplitString(String str, String separator1, String separator2)
Splits a string into multiple parts along the specified separator strings.
Returns: Unknown
Parameters
String str : String to split.
String separator1 : Separator string 1 for parsing the string.
String separator2 : Separator string 2 for parsing the string.
SplitString(String str, String separator1, String separator2, String separator3)
Splits a string into multiple parts along the specified separator strings.
Returns: Unknown
Parameters
String str : String to split.
String separator1 : Separator string 1 for parsing the string.
String separator2 : Separator string 2 for parsing the string.
String separator3 : Separator string 3 for parsing the string.
SplitStringMultipleRecords(String str, String separator)
Splits a string into multiple parts along the separator string.
Returns: Unknown
Parameters
String str : String to split.
String separator : Separator for parsing the string.
SplitStringMultipleRecords(String str, String separator1, String separator2)
Splits a string into multiple parts along the specified separator strings.
Returns: Unknown
Parameters
String str : String to split.
String separator1 : Separator string 1 for parsing the string.
String separator2 : Separator string 2 for parsing the string.
SplitStringMultipleRecords(String str, String separator1, String separator2, String separator3)
Splits a string into multiple parts along the specified separator strings.
Returns: Unknown
Parameters
String str : String to split.
String separator1 : Separator string 1 for parsing the string.
String separator2 : Separator string 2 for parsing the string.
String separator3 : Separator string 3 for parsing the string.
StartsWith(String str, String value)
Determines whether a string starts with a given value.
Returns: Boolean
Parameters
String str : String that will be search
String value : Value that will be searched
Substring(String str, Integer startAt)
Gets all characters from the specified string beginning with startAt position.
Returns: String
Parameters
String str : String whose part will be returned
Integer startAt : Start position of the string to return.
Substring(String str, Integer startAt, Integer noOfChars)
Gets part of the string starting at startAt.
Returns: String
Parameters
String str : String whose part will be returned
Integer startAt : Start position of the string to return.
Integer noOfChars : Maximum number of characters to return.
SYD(Real Cost, Real Salvage, Real Life, Real Period)
Returns the sum-of-years digits depreciation of an asset for a specified period.
Returns: Real
Parameters
Real Cost :
Real Salvage :
Real Life :
Real Period :
TerminateProcess(String processName)
Terminates a running process
Returns: Boolean
Parameters
String processName : Name of the process to check.
TestArrayReturn(String singleVal)
Test return of an array of scalar object
Returns: Unknown
Parameters
String singleVal :
TestComplexParamComplexReturn(Unknown complexParam)
Test input and output of a complex object
Returns: Unknown
Parameters
Unknown complexParam :
TestComplexParamComplexReturn2(String stringParam1, Unknown complexParam)
Test input and output of a complex object, scenario 2
Returns: Unknown
Parameters
String stringParam1 :
Unknown complexParam :
TestComplexParamComplexReturn3(String stringParam1, Unknown complexParam1, Unknown complexParam2)
Test input and output of a complex object, scenario 3
Returns: Unknown
Parameters
String stringParam1 :
Unknown complexParam1 :
Unknown complexParam2 :
TestComplexValParam(Unknown complexParam)
Test a complex input parameter
Returns: Boolean
Parameters
Unknown complexParam :
TestComplexValReturn(String singleVal, Integer intVal)
Test return of complex object
Returns: Unknown
Parameters
String singleVal :
Integer intVal :
TestSingleValAndCollectionParam(String singleVal, Unknown multiVals)
Test input params with array inputs along with a single value
Returns: Boolean
Parameters
String singleVal :
Unknown multiVals :
TestSingleValParam(String singleVal)
Test a single input value
Returns: Boolean
Parameters
String singleVal :
TextLine(String text, Integer lineIndex)
Returns text for the specified line from a multi-line string.
Returns: String
Parameters
String text :
Integer lineIndex :
Ticks(TimeSpan timeSpan)
Returns number of ticks in the specified timeSpan.
Returns: Integer
Parameters
TimeSpan timeSpan : TimeSpan for which number of ticks will be returned.
TimeSpanFromTicks(Integer ticks)
Converts ticks into a TimeSpan.
Returns: TimeSpan
Parameters
Integer ticks : Ticks to be converted to TimeSpan
ToBytes(String str, String codePage)
Returns an array of bytes. Good as an intermediate structure to be used with other functions
Returns: ByteArray
Parameters
String str : The string to convert
String codePage : The code page the string is currently encoded in. Default is UTF-8
ToDate(Integer year, Integer month, Integer day)
Create a new date using year, month, and day provided.
Returns: Date
Parameters
Integer year : Year including the century
Integer month : Month (1-12)
Integer day : Day (1-31)
ToDate(Integer year, Integer month, Integer day, Integer hour, Integer minute, Integer second)
Create a new date using year, month, day, hour, minute, and second provided.
Returns: Date
Parameters
Integer year : Year including the century
Integer month : Month (1-12)
Integer day : Day (1-31)
Integer hour : Hour (0-23)
Integer minute : Minute (0-59)
Integer second : Second (0-59)
ToDate(String dateStr)
Converts string into a date using the current culture.
Returns: Date
Parameters
String dateStr : A string containing date in the local culture format.
ToDate(String dateStr, String format)
Converts string into a date according to the given format.
Returns: Date
Parameters
String dateStr :
String format :
Today()
Gets current system date without the time component.
Returns: Date
Parameters
ToDecimal(Any value)
Converts a value into a decimal number.
Returns: Decimal
Parameters
Any value : Value to be converted
ToInteger(Any value)
Converts a value into integer.
Returns: Integer
Parameters
Any value : Value to be converted
ToJulianDate(Date dateTime)
Converts a date to a Julian Date.
Returns: Decimal
Parameters
Date dateTime : DateTime object for which the Julian Date will be returned.
ToJulianDayNumber(Date dateTime)
Converts a date to a Julian Day Number.
Returns: Integer
Parameters
Date dateTime : DateTime? object for which the Julian Day Number will be returned.
ToLocal(Date dateTime)
Converts specified UTC date time value to local date time value.
Returns: Date
Parameters
Date dateTime : Date which will be converted to local.
ToLower(String str)
Converts the string argument into lowercase characters.
Returns: String
Parameters
String str : String which will be converted.
ToProper(String str)
Converts the string argument into proper case capitalizing first letter of each word.
Returns: String
Parameters
String str : String which will be converted.
ToReal(Any value)
Converts a value into a real number.
Returns: Real
Parameters
Any value : Value to be converted
ToString(Any data, String format)
Converts a an value into string with given format.
Returns: String
Parameters
Any data :
String format : Output format of the value. This method accepts any Microsoft.Net format specification.
ToString(Any value)
Converts a value into string.
Returns: String
Parameters
Any value : Value to be converted
ToString(Date value)
Converts a date value into string using default culture.
Returns: String
Parameters
Date value : Date to be converted
TotalDays(TimeSpan timeSpan)
Returns a real number representing total number of days in the specified timeSpan.
Returns: Real
Parameters
TimeSpan timeSpan : TimeSpan for which total number of days will be returned.
TotalHours(TimeSpan timeSpan)
Returns a real number representing total number of hours in the specified timeSpan.
Returns: Real
Parameters
TimeSpan timeSpan : TimeSpan for which total number of hours will be returned.
TotalMilliSeconds(TimeSpan timeSpan)
Returns a real number representing total number of milliseconds in the specified timeSpan.
Returns: Real
Parameters
TimeSpan timeSpan : TimeSpan for which total number of milliseconds will be returned.
TotalMinutes(TimeSpan timeSpan)
Returns a real number representing total number of minutes in the specified timeSpan.
Returns: Real
Parameters
TimeSpan timeSpan : TimeSpan for which total number of minutes will be returned.
TotalSeconds(TimeSpan timeSpan)
Returns a real number representing total number of seconds in the specified timeSpan.
Returns: Real
Parameters
TimeSpan timeSpan : TimeSpan for which total number of seconds will be returned.
ToTicks(Date dateTime)
Gets the number of ticks in the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which local ticks will be returned.
ToTimeSpan(Integer days, Integer hours, Integer minutes, Integer seconds)
Converts parameters into a TimeSpan.
Returns: TimeSpan
Parameters
Integer days :
Integer hours : Number of hours in TimeSpan
Integer minutes : Number of minutes in TimeSpan
Integer seconds : Number of seconds in TimeSpan
ToTimeSpan(Integer days, Integer hours, Integer minutes, Integer seconds, Integer milliseconds)
Converts parameters into a TimeSpan.
Returns: TimeSpan
Parameters
Integer days :
Integer hours : Number of hours in TimeSpan
Integer minutes : Number of minutes in TimeSpan
Integer seconds : Number of seconds in TimeSpan
Integer milliseconds : Number of milliseconds in TimeSpan
ToTimeSpan(Integer hours, Integer minutes, Integer seconds)
Converts parameters into a TimeSpan.
Returns: TimeSpan
Parameters
Integer hours : Number of hours in TimeSpan
Integer minutes : Number of minutes in TimeSpan
Integer seconds : Number of seconds in TimeSpan
ToTimeSpan(String timeSpanStr, String format)
Converts string into a timeSpan according to the given format.
Returns: TimeSpan
Parameters
String timeSpanStr :
String format :
ToUnixEpoc(Date dateTime)
Gets Unix Epoc value for the specified date time object.
Returns: Integer
Parameters
Date dateTime : Date time object for which Epoc value will be returned.
ToUpper(String str)
Converts string into uppercase characters.
Returns: String
Parameters
String str : String which will be converted.
ToUtc(Date dateTime)
Converts specified date time value to UTC.
Returns: Date
Parameters
Date dateTime : Date which will be converted to UTC.
Trim(String str)
Removes any leading and trailing white spaces from the string argument. Whitespaces include space, new line, tab, and linefeed characters.
Returns: String
Parameters
String str : String which will be trimmed.
TrimEnd(String str)
Removes any trailing white spaces from the string argument. Whitespaces include space, new line, tab, and linefeed characters.
Returns: String
Parameters
String str : String which will be trimmed.
TrimStart(String str)
Removes any leading white spaces from the string argument. Whitespaces include space, new line, tab, and linefeed characters.
Returns: String
Parameters
String str : String which will be trimmed.
TryParseDate(String str, Unknown date)
Out Parameter Test
Returns: Boolean
Parameters
String str :
Unknown date :
UrlEncode(String str)
Returns a string by replacing invalid characters with valid ones for use in a URL
Returns: String
Parameters
String str : The raw string to encode
UtcNow()
Gets the current UTC system date and time.
Returns: Date
Parameters
Week(Date dateTime)
Gets week part of the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which the week will be returned.
WriteBytesToFile(String filePath, ByteArray bytes)
Write bytes to a file. Creates a new file and overwrites the file if already exists.
Returns: Unknown
Parameters
String filePath : Full path of the file.
ByteArray bytes : a byte array to be written to the file.
WriteTextToFile(String filePath, String text)Write string to a file. Creates a new file and overwrites the file if already exists.
Returns: Unknown
Parameters
String filePath : Full path of the file.
String text : Text to be written to the file.
Year(Date dateTime)
Gets year part of the specified date time value.
Returns: Integer
Parameters
Date dateTime : Date for which the year will be returned.
Last updated