Functions
The general form of calling a function is:
| FUNCTION (<param1>, <param2>, ... <paramN>) |
Available functions are grouped as follows:
Mathematics
Stats
Number
| Function | Description |
|---|---|
| ABS(x) | Returns the absolute value of x. If x is not defined, returns an undefined value. |
| INT(x) | Returns the integer part of the number. If x is not defined, returns an undefined value. |
| ROUND(x, n) | Rounds the value of x to n decimal places. If either x or n is undefined, returns an undefined value. |
| ROUNDSIG(x, n) | Rounds the value of x to a given number of (n) significant figures. |
| RANDOM() | Generates a random value. If there are no parameters the generated number is in the range [0,1]. One parameter defines a maximum and the generated number is in the range [0, max]. Two parameters define a minimum and a maximum and the generated number is in the range [min, max]. |
| FIXED(x, n) | Rounds the value of x to n decimal places but keep trailing zeroes. If either x or n is undefined, returns an undefined value. |
| NUMFORMAT_LOCALE(x), NUMFORMAT_LOCALE(x, n) | Returns x as text to n decimal places, formatted using the current locale settings. |
| NUMFORMAT_WORDS(x) | Returns x as spelled out words. For example: 135 becomes "one hundred and thirty-four". |
| NUMFORMAT_ORDINAL(x) | Returns x as text of an ordinal number. For example: 134 becomes "134th". |
Angle
For the trigonometric functions, input values are expected to be in degrees and decimals of a degree (DDD.DDDD) as are the results.
| Function | Description |
|---|---|
| SIN(x) | Returns the SINE of x. |
| COS(x) | Returns the COSINE of x. |
| TAN(x) | Returns the TANGENT of x. |
| ASIN(x) | Returns the ARCSINE of x. |
| ACOS(x) | Returns the ARCCOSINE of x. |
| ATAN(x) | Returns the ARCTANGENT of x. |
| ATAN2(y, x) | Returns the ARCTANGENT of y/x. (if x equals 0, ATAN2 returns 180 if y is positive, -180. if y is negative, or 0 if y is 0.) If either x or y is undefined, the return value is undefined. |
| ADDANGLE(x1, x2, ... xn) | Adds up all passed parameters, making sure the result is in the range [0, 360). |
| SUBANGLE(x1, x2, ... xn) | Subtracts all passed parameters, making sure the result is in the range [0, 360). |
| DMS2DEG(x) | Converts an angle in DMS format (x) to decimal degrees. |
| DEG2DMS(x) | Converts an angle in decimal degrees format (x) to DMS format. |
Text
| Function | Description |
|---|---|
| LEFT(text, n) | Returns the leftmost <n> characters of the text string. |
| RIGHT | Returns the rightmost <n> characters of the text string. |
| SUBSTR(text, m), SUBSTR(text, m, n) | Returns n (or all) characters starting from index m (0 based). |
| MID(text, m), MID(text, m, n) | Returns n (or all) characters starting from index m (1 based). |
| TRIM(text) | Returns the text with white space stripped from the start and end of the text string. |
| REVERSE(text) | Returns the text in reversed character order. |
| REPLACE(text, findStr, replaceStr) | Returns the text with all occurrences of findStr replaced with replaceStr. |
| UPPER(text) | Returns the text in UPPER CASE. |
| LOWER(text) | Returns the text in lower case. |
| TITLE(text) | Returns the text in Title Case. |
| LENGTH(text) | Returns the number of characters in the text string. |
| MATCH(text, match) | Returns true if the text matches the match string. Standard Micromine wildcards can be used in the match string. See: Wildcards |
| MATCH_REGEX(text, regex) | Returns true if the text matches the regular expression. Uses Perl regular expression syntax. See: Regular Expressions |
| STARTSWITH(text, str) | Returns true if the text starts with str. |
| ENDSWITH(text, str) | Returns true if the text ends with str. |
| INDEXOF(text, str), INDEXOF(text, str, m) | Returns character position (0 based) of str within the text, starting at the beginning, or at index m. |
Date
| Function | Description |
|---|---|
| TODAY() | Returns the current date/time. |
| CONSTRUCTDATE(y,m,d), CONSTRUCTDATE(y,m,d,h,n,s) | Creates a date given a year, month, and day. Optionally include time with hours, minutes, and seconds. |
| PARSEDATE(str) | Parses str as a date, or BLANK if failed. |
| PARSEDATE_ADV(str,format) | Parses str as a date using a format string, or BLANK if failed. See Date format conversion below. |
| DATE(date) | Returns a locale formatted date string. |
| TIME(date) | Returns a local formatted time string. |
| DATETIME(date) | Returns a locale formatted date and time string. |
| FORMATDATE(date, format) | Returns a formatted date-time string as defined by the format string, See Date formats below. |
| WEEKDAY(date) | Returns the number of days in the week that the given date falls on. (0=Sunday, 6=Saturday) |
| WEEKDAYNAME(date) | Returns the name of the days in the week that the given date falls on. e.g. "Monday" |
| MONTH(date) | Returns the number of the month that the given date falls in. (1=January, 12=December) |
| MONTHNAME(date) | Returns the name of the month that the given date falls in. e.g. "June" |
| YEAR(date) | Returns the year that the given date falls in. e.g. "1952" |
| YEARDECIMAL(date) | Returns the decimal year that describes the date, where the fractional part is how far through the year it is. e.g. "1952.23" |
| ADDDAYS(date,n) | Adds a number of days to the given date. |
| ADDMONTHS(date,n) | Adds a number of months to the given date. |
| ADDYEARS(date,n) | Adds a number of years to the given date. |
| DAYSBETWEEN(date1,date2) | Returns the number of days between the two dates. |
| MONTHSBETWEEN(date1,date2) | Returns the number of months between the two dates. |
| YEARSBETWEEN(date1,date2) | Returns the number of years between the two dates. |
Date format conversion
The PARSEDATE_ADV function parses a string as a date using a format string, for example:
PARSEDATE_ADV("19/08/02","YYY/MMM/dd")
The function returns a date of the specified format, or returns BLANK in the event of failure. To convert from old-style date formats, use the expressions shown in the following table:
| Old-style date format | Expression |
|---|---|
| YYYYMMDD | =PARSEDATE_ADV([DATE], "yyyyMMdd") |
| DD/MM/YYYY | =PARSEDATE_ADV([DATE],"dd/MM/yyyy") |
| MM/DD/YYYY | =PARSEDATE_ADV([DATE],"MM/dd/yyyy") |
| DD MM YYYY | =PARSEDATE_ADV([DATE],"dd MM yyyy") |
Date formats
The FORMATDATE function returns a formatted date-time string as defined by a date format pattern. For example:
FORMATDATE(TODAY(),"dd/MM/YY")
Date patterns are character strings that comprise a combination of pattern fields and literal text. See: Date Formats
Utility
| Function | Description |
|---|---|
| EVAL(text) | Evaluates text as an expression and returns the result. If the expression cannot be evaluated, a BLANK value is returned. |
Tips
- Be aware of the type conversion in your expression. Remember that apart from a few exceptions, the type of the result is the type of the leftmost operand. Note therefore, that “5" + 5 equals “55" and not 10. Note also that if any operand is undefined the result of the expression will also be undefined.
- Remember that the variable type is the type of the corresponding file field.
- Function and variable names referred to in the expression are matched only when they are actually needed to produce a value. Use the DEFINED function to ensure that a variable exists and has a value before referring to it. 2 + MCAF is unsafe, since MCAF might not exist or might not have a value.
Use 2 + if DEFINED (MCAF) then MCAF else <some_defined_value> fi instead.
- Use parentheses to ensure that the computation order is correct.
- Remember that ALL parts of the 'if' operator are compulsory. Don't forget the 'fi' to mark the end of your conditional operator.