Functions by Category

Aggregate Functions

$average returns the average value of a set of values.

$count counts the number of set members that meet a specified condition.

$max returns the maximum value of a set of values.

$min returns the minimum value of a set of values.

$total sums a set of values.

Character String Manipulation Functions

General

$concat builds a single character string out of separate character strings.

$delete deletes a portion of a character string.

$distinct returns the unique values from a series of successive values.

$fill fills part of a string with a specified character.

$insert inserts a character string into another character string.

$left extracts a segment from the front of a character string.

$length returns the length of a character string.

$position locates a pattern in a character string.

$replace replaces a portion of a character string with another character string.

$right extracts a segment from the end of a character string.

$soundex generates the "sounds-like" value of a string.

$squeeze builds a single character string out of separate strings, inserting a separator string.

$substring extracts a segment of a character string.

$translate replaces segments of a character string with new text.

Blank-trimming

$ltrim trims leading blanks from a character string.

$trim trims leading and trailing blanks from a character string.

$ttrim trims trailing blanks from a character string.

Positioning

$center ($centre) centers a non-blank character string.

$leftjustify left-justifies a character string.

$rightjustify right-justifies a character string.

Case Translating

$tolower converts alphabetic characters to lower case.

$toupper converts alphabetic characters to upper case.

Character Set Translating

$ansitodos translates data from the ANSI character set to the OEM character set.

$dostoansi translates data from the OEM character set to the ANSI character set.

Masking

$mask applies a data mask to a value.

Data Type Manipulation Functions

$money converts a number to a "monetary" format.

$toalpha converts an expression to the alpha data type and sets the length of the result.

$tocharacter converts an expression to the char data type and sets the length of the result.

$tochr converts an ordinal number from 0 to 255 into a character.

$todate converts an expression to the date data type.

$tonumber converts a value into its numeric equivalent.

$toord converts a character into its ordinal number value.

Date Manipulation Functions

$adddays calculates a date value by adding days to (or subtracting days from) a specified date value.

$addmonths calculates a date value by adding months to (or subtracting months from) a specified date value.

$addweeks calculates a date value by adding weeks to (or subtracting weeks from) a specified date value.

$addyears calculates a date value by adding years to (or subtracting years from) a specified date value.

$day extracts the day number associated with a specified date value.

$dayname determines the day name associated with a specified date value.

$month extracts the month number associated with a specified date value.

$monthname determines the month name associated with a specified date value.

$weekday determines the day-of-the-week number associated with a specified date value.

$year extracts the year number associated with a specified date value.

Diagnostic Functions

$compilestatus checks if an application program needs to be recompiled.

$dirpath returns information about the currently accessed application directories.

$filenum returns the number of the disk file associated with a specified object.

$filepath returns the path of the disk file associated with a specified object.

$objstatus indicates if an object has dependent objects.

Logic Functions

$isalphabetic tests if a character string contains only letters.

$isalphanumeric tests if a character string contains only letters and digits.

$isdate tests if a character string is a valid date.

$isdigit tests if a character string contains only digits.

$islower tests if the letters in a character string are lowercase.

$isnumber tests if a character string contains a valid number.

$isodd tests if a number value is odd.

$isupper tests if the letters in a character string are uppercase.

$iswinopen tests if a particular window is currently open for use.

$iszimname tests if a character string is a valid object name.

Mathematics Functions

Trigonometric

$cos calculates the cosine of a number.

$sin calculates the sine of a number.

$tan calculates the tangent of a number.

Transcendental

$acos calculates the arccosine of a number.

$asin calculates the arcsine of a number.

$atan calculates the arctangent of a number.

$atan2 calculates the arctangent (in radians) of the dividend of two numbers.

Hyperbolic

$cosh calculates the hyperbolic cosine of a number.

$sinh calculates the hyperbolic sine of a number.

$tanh calculates the hyperbolic tangent of a number.

Exponential

$exp calculates an exponential value of the natural base (e).

$log calculates the natural logarithm of a number.

$log10 calculates the base 10 logarithm of a number.

$sqrt calculates the square root of a number.

Bit-wise

$and performs a bit-wise "and" of two values.

$or performs a bit-wise "or" of two values.

$not performs a bit-wise "not" of a value.

Miscellaneous

{ } (Case) returns one of a series of values.

$absolute returns the absolute value of a number.

$maxof returns the maximum value from a set of values.

$minof returns the minimum value from a set of values.

$modulus calculates one number modulo another.

$random generates a pseudo-random number, uniformly distributed within the range 0 to 1.

$round rounds a number to the nearest integer.

$truncate truncates a decimal number, making it an integer.

$value provides a default value for an expression that might be $Null.

Set Manipulation Functions

$currentmember returns the position of the current member of a set.

$lastmember returns the number of members in a result set.

System Functions

$getproperty retrieves the setting of certain device properties.

$filebrowse displays the File Open common dialog box.

$fileprint prints a text or bitmap file.

$messagebox presents a message box to the application user and waits for a response.

$printersetup displays the Printer Setup common dialog box.

$screenprint prints a bitmap image of the screen.

$screensave captures a bitmap image of the current screen and writes it to a file.

$setproperty changes the setting of certain device properties.

Time Manipulation Functions

$addhours calculates a time value by adding hours to (or subtracting hours from) a specified time value.

$addminutes calculates a time value by adding minutes to (or subtracting minutes from) a specified time value.

$addseconds calculates a time value by adding seconds to (or subtracting seconds from) a specified time value.

$addticks calculates a time value by adding ticks to (or subtracting ticks from) a specified time value.

$hours extracts "hours" information from a specified time value.

$minutes extracts "minutes" information from a specified time value.

$seconds extracts "seconds" information from a specified time value.

$ticks extracts "ticks" information from a specified time value.