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

ProximityOne -- resources to create and apply insights © ProximityOne