﻿ dBase Functions|Operations

# dBase Functions and Operations

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() 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(,) 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() 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 is true, returns ;  iif false returns Use IIF(,,) and 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 Use Int() Returns numeric Example Int(2.99) returns 2  Int(3) returns 3 LEFT Returns characters from , starting with the first character of . Use Left(,) Returns character string Example LEN Returns the number of characters in . Use Len() Returns numeric Example Len(Trim()) > 0 is true if is not empty(contains data other than spaces). LOWER Converts to lower case. Use Lower() Returns character string Example LTRIM Removes leading spaces from ) Returns character string Example MAX Returns the larger of 2 numeric expressions. Use Max(,) Returns numeric Example Max(-6,13) returns 13 MIN Returns the smaller of 2 numeric expressions. Use Min(,) Returns numeric Example Min(-6,13) returns -6 MOD Returns the remainder from dividing by Use Mod(,) 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 repeated Use Replicate(,) 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 consisting of the rightmost characters. Use Right(,) Returns character string Example ROUND Returns rounded to decimal places Use Round(,) Returns numeric Example Round(1/3,2) returns 1.33 SPACE Returns a character string consisting of spaces. Use Space() Returns character string Len(Space(10)) returns 10 STR Converts a numeric expression to a character string. Use Str([,[,]]) Returns character string Example Notes 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 is omitted, Str() rounds to the nearest integer. If and are omitted, Str() returns 10 digits. SUBSTR Extracts a substring from , beginning at position and containing characters.  If is omitted, the substring continues to the end of . Use Substr(,,[]) 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 Use Trim() Returns character string Example trim(name)='Los Angeles' use above expression with county2k Filter to include only Los Angeles county UPPER Converts to upper case. Use Upper() 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() 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