Contents
dBase Expressions and Functions Supported by Expression Parser
A dBase expression is used to state a condition or query that must be met before an operation (such as selection of dBase records). The expression is a combination of the active dBase field names, constants, logical operators, mathematical operators, functions (see below) and parentheses to group elements in the statement. An example of a more complex statement is:
AGEGRP='00' .and. year='9 ' .and. ((ia_m+ia_f)/(tot_pop))>0.5
Literally interpreted, the above says get all records where
| • | the age group code is 00 (total population) and |
| • | the year code is 9 (2006) and |
| • | ((ia_m+ia_f)/(tot_pop+1))>0.5 -- the percent of American Indian population is 50-percent of more of the total |
Combining Logical Conditions in Expressions. The three logical operators in dBase expressions are .NOT., .AND., and .OR. , illustrated in the following examples:
.NOT. (UPPER(substr(NAME,1,7)) =="BALDWIN") is the same as UPPER(substr(NAME,1,7))<>"BALDWIN"
Test this on the county2k.dbf file -- records where the name field starts with Baldwin are excluded when the above expression is used with Filter.
(.NOT. (UPPER(substr(NAME,1,7)) =="BALDWIN")) .and. (state+county<>'01001')
Test this on the county2k.dbf file -- records where the name field starts with Baldwin and Autauga County, AL (state+ county code 01001) are excluded when the above expression is used with Filter.
dBase functions listed below are supported by the expression parser. Parser also supports the "tokenized" versions of these functions (i.e., parser will recognize the first four or more characters of the function.
Example: SUBSTR() is the same as SUBST() is the same as SUBS().
ABS |
Returns the absolute value (magnitude, disregarding the sign) |
Use |
Abs(<expN>) |
Returns |
numeric |
Example |
Abs(-22) returns 22 |
AT |
Searches for an occurrence of one character string within another, returns the position at which it starts. If not found, returns 0. |
Use |
At(<expC1>,<expC2>) |
Returns |
numeric |
Example |
At('Parish',NAME)>0 |
use At('Parish',NAME)>0 with county2k Filter to include counties having 'Parish' somewhere in the NAME field |
|
CHR |
Accepts a number, returns the corresponding character in the extended character set. |
Use |
Chr(<expN>) |
Returns |
character |
Example |
Chr(65) returns "A" |
DELETED |
Returns .T. (true) if the current record is marked for deletion. |
Use |
Deleted() |
Returns |
Logical |
Example |
IIF(Deleted(), "Yes", "No") returns either "Yes" or "No" customer.SetFilterTo(".NOT.Deleted()"); |
IIF |
iif <expL> is true, returns <exp1>; iif false returns <exp2> |
Use |
IIF(<expL>,<exp1>,<exp2>) <exp1> and <exp2> may be character, date, logical, or numeric, as long as they are both the same type. |
Returns |
character, date, logical, or numeric |
Example |
|
INT |
Returns the largest integer less than or equal to <expN> |
Use |
Int(<expN>) |
Returns |
numeric |
Example |
Int(2.99) returns 2 Int(3) returns 3 |
LEFT |
Returns <expN> characters from <expC>, starting with the first character of <expC>. |
Use |
Left(<expC>,<expN>) |
Returns |
character string |
Example |
|
LEN |
Returns the number of characters in <expC>. |
Use |
Len(<expC>) |
Returns |
numeric |
Example |
Len(Trim(<expC>)) > 0 is true if <expC> is not empty(contains data other than spaces). |
LOWER |
Converts <expC> to lower case. |
Use |
Lower(<expC>) |
Returns |
character string |
Example |
|
LTRIM |
Removes leading spaces from <expC> |
Use |
LTrim(<expC>) |
Returns |
character string |
Example |
|
MAX |
Returns the larger of 2 numeric expressions. |
Use |
Max(<expN1>,<expN2>) |
Returns |
numeric |
Example |
Max(-6,13) returns 13 |
MIN |
Returns the smaller of 2 numeric expressions. |
Use |
Min(<expN1>,<expN2>) |
Returns |
numeric |
Example |
Min(-6,13) returns -6 |
MOD |
Returns the remainder from dividing <expN1> by <expN2> |
Use |
Mod(<expN1>,<expN2>) |
Returns |
numeric |
Example |
Mod(10,3) returns 1 |
RECNO |
Returns the current record number. |
Use |
Recno() |
Returns |
numeric |
Example |
|
REPLICATE |
Returns a string consisting of <expC> repeated <expN> |
Use |
Replicate(<expC>,<expN>) |
Returns |
character string |
Example |
Replicate("X",5) returns "XXXXX" |
Note |
Parser Replicate() accepts only a single character as first parameter. |
RIGHT |
Returns the substring of <expC> consisting of the rightmost <expN> characters. |
Use |
Right(<expC>,<expN>) |
Returns |
character string |
Example |
|
ROUND |
Returns <expN1> rounded to <expN2> decimal places |
Use |
Round(<expN1>,<expN2>) |
Returns |
numeric |
Example |
Round(1/3,2) returns 1.33 |
SPACE |
Returns a character string consisting of <expN> spaces. |
Use |
Space(<expN>) |
Returns |
character string |
Len(Space(10)) returns 10 |
|
STR |
Converts a numeric expression to a character string. |
Use |
Str(<expN>[,<length>[,<decimals>]]) |
Returns |
character string |
Example |
|
Notes |
<length> is the length of the string returned, including decimals, decimal point, and sign. The string will be padded on the left with spaces if needed. If <decimals> is omitted, Str() rounds to the nearest integer. If <length> and <decimals> are omitted, Str() returns 10 digits. |
SUBSTR |
Extracts a substring from <expC>, beginning at position <expN1> and containing <expN2> characters. If <expN2> is omitted, the substring continues to the end of <expC>. |
Use |
Substr(<expC>,<expN1>,[<expN2>]) |
Returns |
character string |
Example |
Substr(NAME,1,3) = 'Los' |
use above expression with county2k Filter to include counties with first 3 characters of name='Los' |
|
TRIM |
Removes spaces from the end of <expC> |
Use |
Trim(<expC>) |
Returns |
character string |
Example |
trim(name)='Los Angeles' |
use above expression with county2k Filter to include only Los Angeles county |
|
UPPER |
Converts <expC> to upper case. |
Use |
Upper(<expC>) |
Returns |
character string |
Example |
Upper (NAME) |
where field NAME takes on value 'Los Angeles', Upper(NAME) returns 'LOS ANGELES' |
|
VAL |
Converts a character expression to numeric Val() ignores leading spaces, but otherwise terminates at the first non-digit character. |
Use |
Val(<expC>) |
Returns |
numeric |
Example |
val(state)>50 .and. val(county)<3 |
use above expression with county2k Filter to include only the first county records in states with FIPS code above 50 |