ACLScript function reference

The topics in this section document the ACLScript functions and their syntax.

For information about the function syntax conventions used in this guide, see ACLScript function syntax conventions.

ACLScript functions by category

ACLScript functions are listed below in eight categories that identify their purpose. If you are working in the Expression Builder, you can filter the functions by the same categories using the Functions drop-down list.

Bit and character functions

ASCII( )

Returns the ASCII character code for a specified character.

BIT( )

Returns the binary representation for the specified byte position in the current record.

BYTE( )

Returns the character stored in the specified byte position in the current record.

CHR( )

Returns the character associated with the specified ASCII character code.

DBYTE( )

Returns the Unicode character located at the specified byte position in a record.

DHEX( )

Converts a Unicode string to a hexadecimal string.

DIGIT( )

Returns the upper or lower digit of a specified Packed data type byte.

HEX( )

Returns a hexadecimal string representing the contents of the specified value.

HTOU( )

Converts a hexadecimal string to a Unicode string. Abbreviation for “Hexadecimal to Unicode”.

MASK( )

Returns the individual bits from the first byte of a character expression.

SHIFT( )

Returns a single character string with the bits in the first character of the input value shifted to the left or right.

Conversion functions

BINTOSTR( )

Returns Unicode character data converted from ZONED or EBCDIC character data. Abbreviation for “Binary to String”.

DEC( )

Returns a numeric expression with the specified number of decimal places.

EBCDIC( )

Returns a string that has been converted to EBCDIC character encoding.

HASH( )

Returns a salted cryptographic hash value based on the input value.

PACKED( )

Returns numeric data converted to the Packed data type.

STRING( )

Converts a numeric value to a character string.

UNSIGNED( )

Returns numeric data converted to the Unsigned data type.

VALUE( )

Converts a character string to a numeric value.

ZONED( )

Converts a numeric value to ASCII zoned data format.

Date and time functions

AGE( )

Returns the age, in days, of a specified date compared to a specified cutoff date, or the current operating system date.

CDOW( )

Returns the name of the day of the week for a specified date or datetime. Abbreviation for “Character Day of Week”.

CMOY( )

Returns the name of the month of the year for a specified date or datetime. Abbreviation for “Character Month of Year”.

CTOD( )

Converts a character or numeric date value to a date. Can also extract the date from a character or numeric datetime value and return it as a date. Abbreviation for “Character to Date”.

CTODT( )

Converts a character or numeric datetime value to a datetime. Abbreviation for “Character to Datetime”.

CTOT( )

Converts a character or numeric time value to a time. Can also extract the time from a character or numeric datetime value and return it as a time. Abbreviation for “Character to Time”.

DATE( )

Extracts the date from a specified date or datetime and returns it as a character string. Can also return the current operating system date.

DATETIME( )

Converts a datetime to a character string. Can also return the current operating system datetime.

DAY( )

Extracts the day of the month from a specified date or datetime and returns it as a numeric value.

DOW( )

Returns a numeric value (1 to 7) representing the day of the week for a specified date or datetime. Abbreviation for “Day of Week”.

DTOU( )

Converts a date to a Unicode string, which allows dates in a numeric format to be displayed in various languages. Abbreviation for “Date to Unicode”.

EOMONTH( )

Returns the date of the last day of the month that is the specified number of months before or after a specified date.

GOMONTH( )

Returns the date that is the specified number of months before or after a specified date.

HOUR( )

Extracts the hour from a specified time or datetime and returns it as a numeric value.

MINUTE( )

Extracts the minutes from a specified time or datetime and returns it as a numeric value.

MONTH( )

Extracts the month from a specified date or datetime and returns it as a numeric value.

NOW( )

Returns the current operating system time as a Datetime data type.

SECOND( )

Extracts the seconds from a specified time or datetime and returns it as a numeric value.

STOD( )

Converts a serial date – that is, a date expressed as an integer – to a date value. Abbreviation for “Serial to Date”.

STODT( )

Converts a serial datetime – that is, a datetime expressed as an integer, and a fractional portion of 24 hours – to a datetime value. Abbreviation for “Serial to Datetime”.

STOT( )

Converts a serial time – that is, a time expressed as a fractional portion of 24 hours, with 24 hours equaling 1 – to a time value. Abbreviation for “Serial to Time”.

TIME( )

Extracts the time from a specified time or datetime and returns it as a character string. Can also return the current operating system time.

TODAY( )

Returns the current operating system date as a Datetime data type.

UTOD( )

Converts a Unicode string to a date, which allows dates in various languages to be displayed in a numeric format. Abbreviation for “Unicode to Date”.

WORKDAY( )

Returns the number of workdays between two dates.

YEAR( )

Extracts the year from a specified date or datetime and returns it as a numeric value.

Financial functions

CUMIPMT( )

Returns the amount of interest paid on a loan during the specified period.

CUMPRINC( )

Returns the amount paid against the principal of a loan for the specified period.

EFFECTIVE( )

Returns the effective annual interest rate on a loan.

FVANNUITY( )

Returns the future value of a series of payments, which represents the sum of the payments plus interest.

FVLUMPSUM( )

Returns the future value of a lump sum after a number of periods.

FVSCHEDULE( )

Returns the future value of an investment calculated at a variable compound rate.

IPMT( )

Returns the size of the interest payment on a loan for a specified period.

NOMINAL( )

Returns the annual nominal interest rate.

NPER( )

Returns the number of periods required to pay off a loan.

PMT( )

Returns the amount of the periodic payment required to pay off a loan.

PPMT( )

Returns the amount of the principal repaid on a loan for a specified period of the term.

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.

Logical functions

BETWEEN( )

Returns a logical value indicating whether the specified value falls within a range.

FIND( )

Returns a logical value indicating whether the specified string is present in a particular field, or anywhere in an entire record.

ISBLANK( )

Returns a logical value indicating whether the input value is blank.

ISDEFINED( )

Returns a logical value indicating whether the specified field exists.

ISFUZZYDUP( )

Returns a logical value indicating whether a string is a fuzzy duplicate of a comparison string.

MAP( )

Returns a logical value indicating if a character string matches a specified format string containing wildcard characters, literal characters, or both.

MATCH( )

Returns a logical value indicating if a value matches any of the values it is compared against.

REGEXFIND( )

Returns a logical value indicating whether the pattern specified by a regular expression occurs in a string.

SOUNDSLIKE( )

Returns a logical value indicating whether a string phonetically matches a comparison string.

TEST( )

Returns a logical value indicating whether a specified string occurs at a specific byte position in a record.

VERIFY( )

Returns a logical value indicating whether the data in a physical data field is valid.

Math functions

ABS( )

Returns the absolute value of a numeric expression. The absolute value of a number is the number without its sign.

COS( )

Returns the cosine of an angle expressed in radians, with a precision of 15 decimal places.

EXP( )

Returns the exponential value (base 10) of a numeric expression with a specified number of decimal places.

INT( )

Returns the integer value of a numeric expression or field value.

LOG( )

Returns the logarithm (base 10) of a numeric expression or field value with a specified number of decimal places.

MAXIMUM( )

Returns the greater value of two numeric values.

MINIMUM( )

Returns the lesser value of two numeric values.

MOD( )

Returns the remainder from dividing two numbers.

NORMDIST( )

Returns the probability that a random variable from a normally distributed data set is less than or equal to a specified value, or exactly equal to a specified value.

NORMSINV( )

Returns the z-score associated with a specified probability in a standard normal distribution.

PI( )

Returns the value of pi to 15 decimal places.

RAND( )

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 whole number for a numeric value.

SIN( )

Returns the sine of an angle expressed in radians, with a precision of 15 decimal places.

TAN( )

Returns the tangent of an angle expressed in radians, with a precision of 15 decimal places.

Miscellaneous functions

FILESIZE( )

Returns the size of the specified file in bytes.

FREQUENCY( )

Returns the expected Benford frequency for sequential leading positive numeric digits to a precision of eight decimal places.

FTYPE( )

Returns a character identifying the data category of a field or variable, or the type of an ACL project item.

GETOPTIONS( )

Returns the current setting for the specified ACL option (Options dialog box setting).

LEADING( )

Returns a string containing a specified number of leading digits.

OFFSET( )

Returns the value of field with the starting position offset by a specified number of bytes.

PROPERTIES( )

Returns properties information for the specified ACL project item.

RECLEN( )

Returns the length of the current record.

RECNO( )

Returns the current record number.

RECOFFSET( )

Returns a field value from a record that is a specified number of records from the current record.

ZSTAT( )

Returns the standard Z-statistic.

String functions

ALLTRIM( )

Returns a new string containing a copy of the specified string with leading and trailing spaces removed.

AT( )

Returns a number specifying where a particular occurrence of a substring starts within a character value.

BLANKS( )

Returns a string containing a specified number of blank spaces.

CLEAN( )

Returns a string where the first invalid character, and all subsequent characters, are replaced with blanks.

DICECOEFFICIENT( )

Returns the Dice’s Coefficient of two specified strings, which is a measurement of how similar the two strings are.

EXCLUDE( )

Returns a string containing a copy of the specified string with the excluded characters removed.

INCLUDE( )

Returns a string with the specified included characters, and with all other characters removed.

INSERT( )

Returns the original string contents with a specified string inserted in a specified location.

LAST( )

Returns a string containing a specified number of characters from the right side of a string.

LENGTH( )

Returns the length of a string.

LEVDIST( )

Returns the Levenshtein Distance between two specified strings, which is a measurement of how much the two strings differ.

LOWER( )

Returns a string with alphabetic characters converted to lowercase.

LTRIM( )

Returns a string with leading spaces removed from the input value.

OCCURS( )

Returns a count of the number of times a substring occurs in a specified character value.

OMIT( )

Returns a string with one or more specified substrings removed.

PROPER( )

Returns a string with the first character of each word set to uppercase and the remaining characters set to lowercase.

REGEXREPLACE( )

Replaces all instances of strings matching a regular expression with a new string.

REMOVE( )

Returns a string with all characters removed, except for a specified list of allowed characters.

REPEAT( )

Returns a string that repeats a substring a specified number of times.

REPLACE( )

Replaces all instances of a specified character string with a new character string.

REVERSE( )

Returns a string with the characters in reverse order.

RJUSTIFY( )

Returns a right-justified string the same length as a specified string, with any trailing spaces moved to the left of the string.

SOUNDEX( )

Returns the soundex code for the specified string, which can be used for phonetic comparisons with other strings.

SPLIT( )

Returns a string containing the text between two separators in the specified string.

SUBSTR( )

Returns a string containing only the specified substring from the input value.

TRANSFORM( )

Reverses the display order of bi-directional text within a specified string.

TRIM( )

Returns a new string containing a copy of the specified string with no trailing spaces.

UPPER( )

Returns a string with alphabetic characters converted to uppercase.

Related concepts
ACLScript function syntax conventions
Related reference
ACLScript command reference


(C) 2015 ACL Services Ltd. All Rights Reserved.