until a second decimal
point, the first non-numeric character, or the end of the expression is encountered. Leading
spaces are ignored. As with all other functions that round, digits between zero and four are
rounded down, and digits between five and nine are rounded up.
AT(search,target)
RAT(search,target)
AT() is a character function used to determine the position of the first occurrence of a
character substring within another string. If you only need to verify whether a substring
exists within another string, use the $ operator. To find the last instance of a substring
within a string, use RAT(). They both return the position of the first instance of search within
target as an integer numeric value. If search is not found, they return zero.
SPACE(number)
Returns a string of number spaces.
LTRIM(string)
RTRIM(string)
ALLTRIM(string)
These functions remove leading and/or trailing spaces
from the string argument, and returns the resultant string. LTRIM removes from the left (leading
spaces), and RTRIM removes from the right (trailing spaces) of the string. ALLTRIM removes both
leading and trailing space characters. To remove carriage returns, line feeds, and tabs as well,
use the MJTRIM function.
PADL(string or date or number,length,[fillchar])
PADR(string or date or number,length,[fillchar])
PADC(string or date or number,length,[fillchar])
These functions pad string on the left (PADL), right (PADR) or on both the left and right (PADC)
with spaces if fillchar is not specified, and with fillchar otherwise. string can be a number instead.
They always return strings with length characters in them. If string is longer than length already,
it is truncated. PADC attempts to centre string in length characters. For example,
PADR("hello",6)+"goodbye" yields "hello goodbye"
PADC("hello",9,"-") yields "--hello--"
PADL(7805,8,"0") yields "00007805"
LEFT(string,length)
Returns the lefthand length characters of string. If length is greater than the number of
characters in string, then string is returned. If length is zero, an empty string is returned ("").
For example, LEFT("Train Timetable",10) = "Train Time".
RIGHT(string,length)
Returns the righthand length characters of string. If length is greater than the number of
characters in string, then string is returned. If length is zero, an empty string is returned ("").
For example, RIGHT("Train Timetable",9) = "Timetable".
SUBSTR(string,start,[length])
Extracts the substring of string, starting at character position start, and going on for length
characters if length is specified, or until the end of the string if it is not.
LEN(string)
Returns the number of characters in string as a number.
LOWER(string)
UPPER(string)
LOWER returns the string argument with all capital letters replaced by lowercase letters. UPPER
returns the string argument with all lowercase letters replaced by capital letters.
REVERSE(string)
Returns the string back to front. For example, REVERSE("Little Lamb") = "bmaL elttiL".
CONTAINS(field,searchstring)
ONLY USE THIS FUNCTION IN FILTERS AND INDEXES - NOWHERE ELSE
Returns true or false as to whether searchstring exists in field.
The search conditions used for full text searches are character strings composed of words,
phrases, logical operators, and parentheses for grouping precedence. Aside from white space
characters, which are considered to be delimiters in search strings, special characters include
the double quote (0x22) used for delimiting phrases, the asterisk (0x2A) used for prefix,
postfix, and substring matching, and parentheses (0x28 and 0x29). The reserved words include
the logical operators OR, AND, NOT, NEAR.
The precedence of the logical operators from lowest to highest is OR, AND, NOT, NEAR. You can
use parentheses to change the precedence grouping. For example, the search condition 'meeting
and request or changelog' finds records that have both the words "meeting" and "request" or the
word "changelog". Changing the precedence with parentheses to 'meeting and (request or
changelog)' causes the search condition to find records that have the word "meeting" and either
of the words "request" or "changelog".
The logical OR operator produces a "true" result when either operand evaluates to true. The
logical AND operator produces a true result only when both of its operands evaluate to true.
The logical NOT operator produces a true result when its single operand has a false result. The
NEAR operator (proximity operator) is similar to the AND operator in that both of its operands
must be found. In addition, it requires that its operands be within a certain physical
distance of each other for it to return a true result. The default distance for the NEAR
operator is 8 words. To use a proximity value other than the default, specify the distance as a
parameter to the NEAR operator. For example ' medical near(15) doctor' will evaluate to true
for records where the word "medical" is within 15 words of "doctor".
Search words that do not have logical operators specified are assumed to have an implied AND
operator between them. For example, the following two search conditions are equivalent:
'mechanical engineer'
'mechanical AND engineer'
And the following two are equivalent:
'computer programmer or software developer'
'(computer AND programmer) OR (software AND developer)'
Individual search words that are not enclosed in double quotes can be searched for as exact
matches, prefix matches, postfix matches, or substring matches. This behavior is controlled
through the use of the asterisk (*) character.
Exact match. Simply specify the word in the search condition. The word will be matched if
it is found exactly as given in the text. For example, the search word "special" will match
only "special" in the text (or upper case versions if it is not a case sensitive index).
Prefix match. Place an asterisk at the end of the word to match all words that begin with
the given characters. For example, the search word "special*" will match "special",
"specialty", "specialization", etc.
Postfix match. Place an asterisk at the beginning of the word to match all words that end
with the given characters. For example, the search word "*ation" will match words "station",
"specialization", "citation", etc.
Substring match. Place an asterisk at both the beginning and end of the word to match all
words that contain the given characters. For example, the search word "*lock*" will match the
words "locker", "antilock", "blocking", etc.
Double quotes are used as phrase delimiters. In addition, special characters can be enclosed
inside double quotes to ensure that they are unchanged by the search condition parser. For
example, to search for a parenthesis, it is necessary to enclose it in double quotes in order
to keep it from being treated as a precedence operator in the search condition itself. To
search for a physical double quote, use two of them in a row. Single quotes are not treated as
a special case by the FTS parser, but they are the text delimiter in SQL statements. This
means that if you use single quotes inside FTS search conditions in an SQL statement, you must
include two of them in a row.
Spaces are considered to be delimiters in search conditions. If you have a special situation in
which spaces are not delimiters in the text and are part of search words, then it may be
necessary to enclose the search words in double quotes in order to preserve the spaces. Multi-
word phrases in search conditions match identical phrases in the text being searched. For
example, the search condition ' "alpine skiing" ' will match only records that have the exact
words "alpine" and "skiing" in that order in the text with no other non-noise words between
them.
If you use the NEAR operator with sub-expressions, you may need to use the optional form of the
operator with the parentheses after it to avoid ambiguity in the expression. For example, the
condition "a near (b and c)" is not valid because the left parenthesis is interpreted as the
opening of the NEAR proximity parameter. The condition would need to be written as "a near() (b
and c)". The empty parentheses after the NEAR operator remove the ambiguity; because they are
empty, the default proximity value is used. You can also specify a proximity value: "a near
(25) (b and c)".
The following is a more formal grammar definition for the search conditions. The square
brackets indicate optional items.
search-condition ::= | OR
and-term ::= | [AND]
not-term ::= | NOT
near-term ::= | NEAR []
near-distance ::= ( [integer] )
value-term ::= ( ) |
simple-term ::= text | text* | *text | *text* | "phrase"
Date Functions
CTOD(datestring)
Converts datestring to a date. If datestring cannot be converted, an empty date is returned.
STOTS(datetimestring)
Converts datetimestring to a timestamp. If datetimestring cannot be converted, an empty timestamp is returned.
datetimestring should be in the format YYYYMMDD hh:mm:ss.nnn (nnn is milliseconds)
CTOTS(datetimestring)
Converts datetimestring to a timestamp. If datetimestring cannot be converted, an empty timestamp is returned.
datetimestring should be in the format DD/MM/YYYY hh:mm:ss. For example,
"whenitwas<CTOTS( ’30/9/1996 10:30am’)" could be used as a filter for records whose whenitwas TIMESTAMP field is before 30th
September 1996 at 10.30 in the morning.
TSTOD(timestamp field)
Converts a Timestamp field to a date. Empty dates return the string '00/00/0000'.
DTOC(date)
Converts date into a string in the format DD/MM/YYYY. If date is an empty date, DTOC returns
a string of 10 spaces.
DTOS(date)
Converts date into a string in the format YYYYMMDD. If date is an empty date, DTOS returns
a string of 8 spaces.
STOD(datestring)
Converts datestring to a date, where datestring is in the format YYYYMMDD.
DATE()
Returns the current system date as a date.
TIME()
Returns the current system time as an 8 character string in the 24-hour format HH:MM:SS. This
function does not seem to take into account daylight savings time.
DAY(date)
MONTH(date)
YEAR(date)
These functions return a number representing the day, month or year of date respectively.
YEAR() always returns a 4-digit year. If date is empty, then these functions all return zero.
Miscellaneous Functions
DESCEND(expression)
Returns representation of the opposite of expression. It is mostly used
for descending order indices. If expression is a number, the result is the negative of the number.
If expression is a logical value, then the NOT of this value is returned. If it is a date, then a
numerical representation of the opposite of this date is returned. You can make a descending index
of a field DATE, by using the index expression DESCEND(DATE). If the expression is a string, the
return value is formed so that collation happens in the opposite direction to normal. You can try out
a few expressions using the Evaluate Expression option on the Record menu to see what return values
are given for various input expressions.
EMPTY(expression)
Returns true if the expression is empty, and false otherwise. Empty is defined as :-
Character Spaces, tabs, CR/LF, or an empty string ("")
Numeric 0
Date CTOD("") or STOD("")
Logical False (.F.)
DELETED()
Returns true or false as to whether the current database record is deleted.
RECNO()
Returns the current database record number. This is usually the slot in the database that the
record is physically stored in. It returns the same number for the same record, no matter what order
the database is in.
IF(condition,expression1,expression2)
If condition is true, then this returns whatever expression1 evaluates to, otherwise it returns
whatever expression2 evaluates to. For example,
IF(EMPTY(ENTRYDATE),DATE(),ENTRYDATE)
Back to the Top
Back to DKapture Help Index
© DK Computing