The following tables summarize the various Analyzer functions by category:
ASCII() |
Returns the ASCII decimal value of the first character of an expression |
BIT() |
Returns a string of 0s and 1s representing a specified byte position in the current record |
BYTE() |
Returns the byte at a specified position in the current record |
CHR() |
Returns the ASCII character corresponding to the decimal value that you specify |
DIGIT() |
Returns the upper or lower digit of a specified packed byte |
HEXADECIMAL() |
Returns a string of hexadecimal values representing the contents of a field |
MASK() |
Extracts individual bits from the first byte of a character expression |
SHIFT() |
Shifts the first character of an expression left or right by a specified number of bits |
DECIMALS() |
Returns a numeric expression with a specified number of decimal places (from a numeric or elapsed time value) |
EBCDIC() |
Converts a string into EBCDIC characters |
PACKED() |
Converts numeric data into a PACKED data type of a specified length |
STRING() |
Converts a numeric or elapsed time expression or field into a string of characters |
UNICODE() |
Converts ASCII data into UNICODE data (UTF-16) |
UNSIGNED() |
Converts numeric data into an UNSIGNED data type of a specified length |
VALUE() |
Converts a character expression into the numeric equivalent |
ZONED() |
Converts numeric data to a ZONED data format |
AGE() |
Returns the age (in days) of a date compared to a specified cutoff date or the current system date |
CDOW() |
Returns the name of the day of the week for a specified date |
CMON() |
Returns the name of the month referenced by the supplied date as a string of specified length |
CTOD() |
Converts a character or numeric expression into a valid datetime expression for comparison with other datetime values |
DATE() |
Converts a date expression, the current system date or a DateTime field value into a character string readable as a date |
DAY() |
Returns a numeric value representing the day portion for a specified date |
DOW() |
Returns a numeric value representing the day of the week for a specified date |
EOMONTH() |
Returns a DateTime value representing the end of month for the specified date. The returned DateTime value represents 23:59:59 on that day |
GOMONTH() |
Returns the exact DateTime that is plus or minus the number of months from the specified DateTime. |
HOUR() |
Returns a numeric value representing the hour portion for a specified time using a 24-hour clock |
MAXIMUM() |
Returns the largest date in a set |
MINIMUM() |
Returns the smallest date in a set |
MINUTE() |
Returns a numeric value representing the minute portion for a specified time |
MONTH() |
Returns a numeric value representing the month portion for a specified date |
NOW() |
Returns the current system date and time as a datetime value |
NTOD() |
Returns the date represented by supplied numeric year, month and day. Specified year must include century. |
NTOT() |
Returns the time represented by supplied numeric hour, minutes and seconds. Hour must be specified using 24-hour clock. |
SECOND() |
Returns a numeric value representing the second portion for a specified date |
TIME() |
Converts a time expression, the current system time or a DateTime field value into a character string readable as a time in 24 hour clock mode. |
TODAY() |
Returns the current system date only as a datetime value |
WEEKEND() |
Returns T if the specified datetime field is a Saturday or Sunday, F if not. |
WORKDAY() |
Returns the number of work days between two date values (inclusive of the start and end dates) - this function can be customized to specify local non-work days, otherwise Saturday and Sunday are deemed non-work days. |
YEAR() |
Returns a numeric value representing the four-digit year portion for a specified date |
EFFECTIVE() |
Returns the effective annual interest rate on a loan |
FVANNUITY() |
Returns the future value of a series of payments |
FVLUMPSUM() |
Returns the future value of a lump sum |
NOMINAL() |
Returns the annual nominal interest rate |
NPER() |
Returns the periods required to pay off a loan |
PMT() |
Returns the periodic payments required to pay off a loan |
PVANNUITY() |
Returns the present value of a series of payments |
PVLUMPSUM() |
Returns the present value of a lump sum due in a number of periods |
RATE() |
Returns the interest rate per period |
ALLTRIM() |
Removes leading and trailing blanks from character strings. Does not affect blanks inside the string |
ARRANGE() |
Re-arranges characters or optionally whole words within a string in descending order based on the host machines character set |
BETWEEN() |
Tests whether input values fall within a specified range |
COMPACT() |
By default, removes multiple consecutive blanks in a string with a single blank. Optional character OR character string can be specified to remove multiple consecutive occurrences of a character OR character string with a single occurrence. |
DIFFERENCE() |
Compares two strings and returns a positive whole number. A zero indicates that two strings are identical, a small number indicates that two strings are very similar. A value equal to the length of the longer string indicates that two strings are different. Case sensitive unless turned off. |
EXCLUDE() |
Compares two strings and returns a string containing only those characters not common to both |
FIND() |
Searches for a string inside one or more fields or the entire record. Also supports Google type search for a literal. |
FORMAT() |
Returns a string the same length of the source string, containing the same characters except that all alphabetic letters are replaced with either upper case X’s or lower case x’s (depending on case encountered) and all numbers are replaced with 9’s. Opposite of MAP() function. Useful for determining the format of a string or locating data with same format. |
INCLUDE() |
Compares two strings and returns a string containing only those characters common to both |
LISTFIND() |
Searches for a list of strings inside one or more fields or the entire record. |
LOWER() |
Converts all alphabetic characters of an expression to lower case |
LTRIM() |
Removes blanks from the beginning of a string |
MAP() |
Compares a string to a specified format |
MATCH() |
Compares an expression or field value (character, date, or numeric) to specified expressions, field values or variable arrays of the same type to determine whether there is at least one match |
NEAR() |
Tests whether the difference between two fields falls within a specified difference (used on pairs of numeric, date or character fields) |
NORMALIZE() |
Replaces all contiguous non-alphanumeric characters (except blanks) with a blank, replaces all International characters with English base equivalents, trims leading blanks and compacts all contiguous blank spaces into a single blank space, and upper cases the entire string - also allows option to retain specified characters and to make text substitutions. |
REMOVE() |
Removes unwanted characters from a character string |
SIMILAR() |
Tests whether the difference between two fields falls within the specified Damerau-Levenshtein distance (used on pairs of numeric, date or character fields) Prior to testing the difference, this function takes a pair of character strings (or converts a specified pair of numeric or date values to character strings), upper cases the strings, removes all blanks, any leading zeros and punctuation, and matches similar looking characters (e.g. 1 for I, 0 for O). |
SMARTSEARCH() |
Searches for one or more strings in all columns in the active View or only within highlighted columns in the active View. Columns can be data, computed or related columns. For use in View filters only, not in procedures. |
SORTNORMALIZE() |
Replaces all contiguous non-alphanumeric characters (except blanks) with a blank, replaces all International characters with English base equivalents, trims leading blanks and compacts all contiguous blank spaces into a single blank space, and upper cases the entire string - also allows option to retain specified characters and to make text substitutions. Finally, re-arranges remaining result in descending order of whole words based on using any remaining blanks as word separators. |
SOUNDEX() |
Returns a four-character value for a string, which can be compared with the value of another string to see if both strings are phonetically similar |
SOUNDSLIKE() |
Indicates whether two strings sound phonetically alike |
TEST() |
Searches for specified character strings at specified locations in a record |
TRIM() |
Removes blanks from the end of a string |
UPPER() |
Converts all alphabetic characters of an expression to upper case |
BETWEEN() |
Tests whether input values fall within a specified range |
FIND() |
Searches for a string inside one or more fields or the entire record. Also supports Google type search for a literal. |
ISBLANK() |
Tests whether an input value is entirely blank |
ISDEFINED() |
Tests whether a field or variable exists or has been chosen in a dialog |
LISTFIND() |
Searches for a list of strings inside one or more fields or the entire record. |
MAP() |
Compares a string to a specified format |
MATCH() |
Compares an expression or field value (character, date, or numeric) to specified expressions, field values or variable arrays of the same type to determine whether there is at least one match |
NEAR() |
Tests whether the difference between two fields falls within a specified difference (used on pairs of numeric, date or character fields) |
SIMILAR() |
Tests whether the difference between two fields falls within the specified Damerau-Levenshtein distance (used on pairs of numeric, date or character fields) Prior to testing the difference, this function takes a pair of character strings (or converts a specified pair of numeric or date values to character strings), upper cases the strings, removes blanks and any leading zeros and punctuation, and matches similar looking characters (e.g. 1 for I, 0 for O). |
SMARTSEARCH() |
Searches for one or more strings in all columns in the active View or only within highlighted columns in the active View. Columns can be data, computed or related columns. For use in View filters only, not in procedures. |
TEST() |
Searches for specified character strings at specified locations in a record |
WEEKEND() |
Returns T if the specified datetime field is a Saturday or Sunday, F if not. |
VERIFY() |
Searches for invalid data |
ABSOLUTE() |
Returns the absolute value of a numeric expression |
ACOSINE() |
Returns the arc cosine of an angle expressed in radians |
ASINE() |
Returns the arc sine of an angle expressed in radians |
ATANGENT() |
Returns the arc tangent of an angle expressed in radians |
AVERAGE() |
Returns the average value from a set of numeric values |
COSINE() |
Returns the cosine of an angle expressed in radians |
EXPONENT() |
Returns the exponential value (base 10) of a numeric expression |
FREQUENCY() |
Returns the expected Benford frequency for sequential leading positive numeric digits to a precision of eight digits |
INTEGER() |
Returns the integer value of a numeric expression |
LOGARITHM() |
Returns the logarithm (base 10) of a numeric expression |
MAXIMUM() |
Returns the greater of two numeric parameters |
MINIMUM() |
Returns the lesser of two numeric parameters |
MOD() |
Returns the remainder of a division calculation |
RADIANS() |
Returns the numeric radians for an angle expressed in degrees |
RANDOM() |
Returns a random number between zero and the value of a numeric expression |
ROOT() |
Returns the square root of a numeric expression |
ROUND() |
Returns a rounded number from a numeric expression |
SINE() |
Returns the sine of an angle expressed in radians |
TANGENT() |
Returns the tangent of an angle expressed in radians |
TOTAL() |
Returns the total value for a set of numeric values |
ZSTAT() |
Calculates the standard Z-statistic for use in many problem-solving tasks, including digital analysis |
FILESIZE() |
Returns the size of a specified file in bytes |
FTYPE() |
Returns the type for a field, variable or project item |
LEADING() |
Returns a character string containing a specified number of leading digits from a numeric field. Intended for use with the Benford Command |
OFFSET() |
Adjusts the starting position of a field within the current record |
RECLEN() |
Returns the length of the current record |
RECNO() |
Returns the current logical record number |
RECOFFSET() |
Returns the value of a field at a specified number of records from the current record |
ALLTRIM() |
Removes leading and trailing blanks from character strings. Does not affect blanks inside the string. Optionally removes specified leading and trailing characters. |
ARRANGE() |
Re-arranges characters or optionally whole words within a string in descending order based on the host machines character set |
AT() |
Locates where a specified occurrence of a character string starts within another character expression or field |
BLANKS() |
Creates a character string of specified length consisting solely of blanks. Compare with “REPEAT()” below. |
CLEAN() |
Discovers any invalid characters in a string and replaces them and all subsequent characters with blanks |
COMPACT() |
By default, removes multiple consecutive blanks in a string with a single blank. Optional character OR character string can be specified to remove multiple consecutive occurrences of a character OR character string with a single occurrence. |
DIFFERENCE() |
Compares two strings and returns a positive whole number. A zero indicates that two strings are identical, a small number indicates that two strings are very similar. A value equal to the length of the longer string indicates that two strings are different. Case sensitive unless turned off. |
EXCLUDE() |
Compares two strings and returns a string containing only those characters not common to both |
FORMAT() |
Returns a string the same length of the source string, containing the same characters except that all alphabetic letters are replaced with either upper case X’s or lower case x’s (depending on case encountered) and all numbers are replaced with 9’s. Opposite of MAP() function. Useful for determining the format of a string or locating data with same format. |
INCLUDE() |
Compares two strings and returns a string containing only those characters common to both |
INSERT() |
Inserts characters or blanks at a specified location in a character string |
LAST() |
Captures a specified number of characters from the end of a string (same as RIGHT() function) |
LEFT() |
Captures the specified number of characters from the left of the string |
LENGTH() |
Calculates the number of characters in a specified string |
LFILL() |
Fills the leading portion of a string with specified character, padding to length specified. |
LOWER() |
Converts all alphabetic characters of an expression to lower case |
LTRIM() |
Removes blanks from the beginning of a string. Optionally removes specified leading characters. |
NORMALIZE() |
Replaces all contiguous non-alphanumeric characters (except blanks) with a blank, replaces all International characters with English base equivalents, trims leading blanks and compacts all contiguous blank spaces into a single blank space, and upper cases the entire string - also allows option to retain specified characters and to make text substitutions. |
OCCURS() |
Counts the number of times a character string occurs within a longer string |
PROPER() |
Converts alphabetic characters in a string to upper case and lower case, in a manner appropriate for proper names |
REMOVE() |
Removes unwanted characters from a character string |
REGEXFIND() |
Uses regular expressions to perform complex searches in order to find data in strings |
REGEXREPLACE() |
Uses regular expressions in order to make complex replacements within strings |
REPEAT() |
Creates a character string that repeats a constant value a specified number of times. Compare with “BLANKS” above. |
REPLACE() |
Replaces all occurrences of a character string with another |
REVERSE() |
Reverses the order of characters in a string |
RIGHT() |
Captures the specified number of characters from the right of the string, same as LAST() function |
RJUSTIFY() |
Right-justifies a string to a specified length and moves any trailing blanks to the front of the string |
SORTNORMALIZE() |
Replaces all contiguous non-alphanumeric characters (except blanks) with a blank, replaces all International characters with English base equivalents, trims leading blanks and compacts all contiguous blank spaces into a single blank space, and upper cases the entire string - also allows option to retain specified characters and to make text substitutions. Finally, re-arranges remaining result in descending order of whole words based on using any remaining blanks as word separators. Ideal for address fields |
SOUNDEX() |
Returns a four-character value for a string, which can be compared with the value of another string to see if both strings are phonetically similar |
SPLIT() |
Returns a specified segment of a character string delimited by separators such as spaces or commas |
SUBSTRING() |
Returns a specified portion of a character expression or field |
TRANSFORM() |
Allows Hebrew characters in a string to display properly |
TRIM() |
Removes blanks from the end of a string. Optionally removes specified trailing characters. |
UPPER() |
Converts all alphabetic characters of an expression to upper case |