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

© Copyright 2023, Astera Software