ACL Functions

Function Description Example
Changes the case of a character string UPPER(dave) = “DAVE”
LOWER(‘DAVE’) = “dave”
PROPER(“DAVE code”)= “Dave Code”
MATCH(comparison_value, test1, test2, <test3…>) Compare value with a list of values; T if at least one match MATCH(“DAVE”, NAME, INIT, LNAME) – T if NAME=”DAVE” or INIT=”DAVE” or LNAME=”DAVE”
ABS(value) Absolute value of number ABS(-12.3) = 12.3
SUBSTR(string, start, length) Returns substring of a string SUBSTR(“GARY”,2,2) = “AR”
SUBSTR(“DAVE”,4,1) = “E”
CTOD(field, date format) Convert text to date CTOD(“20150301”) = 2015/03/01
Date(date) Convert date to text DATE(2015/03/01) = “2015/03/01”
Determine Day of Week DOW(`20150301`) = 1
CDOW(`20150301`) = “SUN”
STRING(value, length) Converts Numeric Value to a string STR(123.5,5) = ” 123.5″
STR(645,1) = “5”
Value(string, decimals) Converts string to a numeric value VALUE(“123”,2) = 123.00
VALUE(“12.3”,2) = 12.30
MOD(number, divisor) Remainder of Number when divided by divisor MOD(25,5) = 0
MOD(14,3) = 2
INCLUDE(string, chars_to_include) Removes all characters except include string INCLUDE(“9A6″,”0987654321″)=”96”
INCLUDE(“A55A2″,”A”) = “AA”
EXCLUDE(string, chars_to_exclude) Returns all characters except exclude string EXCLUDE(“N/A”,”/”) = “NA”
EXCLUDE(“99-OCT”,”-“) = “99OCT”
AGE(date1, date2) Number of days between date1 and date2 Age(`20150331`,`20150415`) = 15
LEN(string) Calculate length of a string LEN(“David”) = 5
LEN(“Lindsay”) = 7
LTRIM(string) Removes leading blanks LTRIM(” ABC”) = “ABC”
TRIM(string) Removes trailing blanks TRIM(“DAVE “) = “DAVE”
TRIM(“ANN “) = “ANN”
ALLTRIM(string) Removes leading and trailing blanks ALLTRIM(” Johnny “) = “Johnny”
Verify(field) Checks integrity of field with field type VERIFY(DATE) – T if record contain valid date
BETWEEN(string, min, max)
Logical (T or F)
Selects only records where the value is >= min and <= max BETWEEN(Name,”D”,”F”) = all names starting with D, E or F)
BETWEEN(Amount,2,6) = amounts >=2 and <=6
FIND(string, field) Find a string FIND(“Dave”, NAME) – T if NAME contains “Dave” (not case sensitive)
MAX(num1,num2) Returns Maximum of two values MAX(2,5) = 5
MAX(8,2) = 8
MIN(num1, num2) Returns Minimum of two values MIN(2.3,7) = 2.3
MIN(8,3) = 3
AT(pos,#,search_for_string) Returns position of search_for_string in field AT(2,”B”,”ABBA”) = 3
AT(1,”0″,”1234″) = 0
OCCURS(string, search_for) Counts the number of times the search_for string occurs in the field OCCURS(‘ALLAN’,’A’) = 2
SPLIT(string, separator, segment) Creates a field based on separator and specified segment SPLIT(“Johnny,Shilo,1233 Main St,Ottawa,”,”,3) = “1233 Main St”
RECNO() Record Number recno() < 20
LAST(string, length) The last ‘x’ characters of the field LAST(“Johnny Shilo”,5) = “Shilo”
INSERT(string, insert_text, location) Inserts new text into field at specified location INSERT(Phone,’-‘,4)
9960784 = 996-0784
REPLACE(string, old_text, new_text) Replaces specified character with new characters REPLACE(“2015-03-01″,”-“,”/”) = “2015/03/01”
MAP(string, format)
Logical (T or F)
Test string to see if it is in format specified: x-alph; 9-numeric; ? – any char; \ – a literal MAP(Date,’99/99/99″) checks date field to see if data is in format 99/99/99.
MAP(’99-03-26′,’99/99/99′) = False
MAP(’99/03/26′,’99/99/99′) = True
MAP(‘K1A0K2′,’x9x9x9’) = True
REPEAT(string, count) Repeats given string ‘x’ times REPEAT(“A”,3) = “AAA”
BLANKS(count) Creates a character string of ‘x’ blanks BLANKS(3) = ” “
Logical (T or F)
Identify records where field is blank ISBLANK(Phone) = records with no phone number
TEST(position, string) T if specified position contains string TEST(72,”.”) – T is position 72 contains a decimal point (dot)
REVERSE(string) Reverse the character string REVERSE(“Music”) = “cisuM”
RJUSTIFY(string) Right justifies field RJUSTIFY(“ABC “) = ” ABC”