| Function | Description | Example |
|---|---|---|
| UPPER() LOWER() PROPER() |
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” |
| DOW(Date) CDOW(date) |
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 OCCURS(“YNNYYN”,”Y”) = 3 |
| 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” REPEAT(“AB”,2) = “ABAB” |
| BLANKS(count) | Creates a character string of ‘x’ blanks | BLANKS(3) = ” “ |
| ISBLANK(string) 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” |
