<< Click here to display Table of Contents >> Navigation: Database Operations:

dBase Functions and Operations

Contents

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&ggt;
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