Relational Expressions

Three types of relational expressions are discussed in this section:

Relational operators

Relational operators used in DataBasic are shown in the following table.

Symbol

Operation

< or LT

less than

> or GT

greater than

<=, LE, =< or #>

less than or equal to

>=, GE, => or #<

greater than or equal to

= or EQ

equal to

#, <>, ><, or NE

not equal to

MATCH{ES}

pattern matching

MATCHFIELD

pattern matching

A relational operation typically evaluates to 1 if the relation is true and 0 if the relation is false.

Relational operators have lower precedence and are, therefore, evaluated after all arithmetic and string operations have been performed, unless they are placed in parentheses in which case the precedence is raised.

Arithmetic relations

An arithmetic relation is a pair of arithmetic expressions separated by any one of the relational operators. For example:

Z = X > 4 If X greater than 4, Z is set to one (true), but if X less than or equal 4, Z is set to zero (false).

A = B < 4*2 If B is less than 4 times 2 (8), A is set to one (true), but if B is greater than or equal to 8, A is set to zero (false).

P = Q NE 4+6 If Q is not equal to 10 (4 plus 6), P is set to one (true), but if Q is equal to 10, P is set to zero (false).

String relations

A string relation comprises a pair of string expressions, or a string expression and an arithmetic expression, separated by any one of the relational operators.

Note

Two arithmetic expressions are always evaluated as an arithmetic relation.

Comparing two strings of the same length

In string relations, the characters in the two strings are compared one at a time from left to right. Each successive pair of characters is evaluated by the specified relational operator according to their numeric ASCII code values.

The first pair of ASCII character codes values to meet the relational condition, specified by the operator, causes the string relation to evaluate to 1 (true). If none of the corresponding character comparisons meet the specified condition, the string relation evaluates to 0 (false). For example:

"AAB" > "AAA"

evaluates to 1 (true), because the ASCII value of the third character B (66) is greater than the ASCII value of A (65), while:

"AAB" > "ABC"

evaluates to 0 (false), as the ASCII value of the second character in the first string (A, ASCII value 65) is less than that of the second character in the second string (B, ASCII value 66).

Comparing two strings of different length

If two strings are not the same length, but the shorter string is identical to the beginning of the longer string, the longer string is considered to be greater than the shorter string. The right-most overlapping' character of the longer string is in fact compared with ASCII null producing a "greater than" condition.

Note

The null string, in general, is treated as less than zero.

For example:

"STRINGS" GT "STRING"

This relation evaluates to 1 (true) with the ASCII equivalent of S being 83 and the ASCII equivalent of the null string zero.

Comparing a string with an arithmetic expression

If a string is compared to an arithmetic expression, the result of the arithmetic expression is treated as a string expression. For example:

24 * 6 GT "14%"

evaluates to 1 (true). The arithmetic expression evaluates to "144" which is compared with "14%". The ASCII value of 4 (51) is greater than % (37), hence the expression is 'true'.

Further examples of string relational expressions are provided below:

"AND" EQ "BUT"

evaluates to 0 (false), because the ASCII value of A (65) does not equal the ASCII value of B (66).

"BILL" < 5431

evaluates to 0, because the ASCII value of B (66) is greater than the ASCII value of 5 (53).

12*4 > "AB"

evaluates to 0 (false), because the ASCII values of 4 (52)is less than the ASCII value of A (65). Remember. (The result 12 times 4 equals 48 is evaluated first).

12*9 # "108"

evaluates to 0 (false), because the result of 12 times 9 is 108 which is equal to the string "108" 

0 > ""

evaluates to 1 (true). The ASCII value of decimal zero is 48 which is greater than the ASCII value of null (0).

Case insensitivity

If data case-insensitivity mode is selected, case is ignored when evaluating string relations.

Pattern matching

MATCH{ES} operator

The MATCH (or MATCHES) relational operator is used to compare a string expression to a pattern phrase consisting of one or more patterns separated by value marks (although this behaviour can be changed with the MATCH.DELIM compatibility option).

Each pattern can include fields such as literal strings, a (possibly indefinite) number of characters, or a range of characters, either individually or in any combination. Characters can be specified as numeric, non-numeric, alphabetic, non-alphabetic, or indeterminate. The pattern must account for all characters in the expression for it to be a valid match.

If the expression matches at least one of the patterns, the string relation evaluates to the ordinal number (1, 2, 3, and so on) of the first (or only) matching pattern in the pattern phrase, unless the EXT.MATCH compatibility option is not set in which case it simply evaluates to 1 (meaning TRUE). If there is no match it evaluates to 0 (meaning FALSE).

Syntax

expression MATCH{ES} "pattern{]pattern}…"

Note

The complete pattern phrase must be enclosed in quote characters. If any pattern includes a literal string the pattern phrase should be enclosed in double quotes, with single quotes around the literal string (or vice versa).

Syntax elements

expressionThe string expression to be matched.

patternA string value that specifies the pattern of characters to be found. It can include any number and combination of the following pattern fields:

'literal'Specifies a literal string of characters; such strings are case sensitive.

nNSpecifies a pattern of n numeric characters, where n is an integer.

nASpecifies a pattern of n alphabetic characters, where n is an integer.

nXSpecifies a pattern of n characters, where n is an integer.

The following pattern fields were added in Reality V15.2. For backward compatibility they are valid only if the EXT.MATCH option is set.

...Specifies a pattern of any number of characters, equivalent to 0X (but see note below). Note that the pattern consists of three periods, not an ellipsis.

~~nNSpecifies a pattern of n non-numeric characters, where n is an integer.

~~nASpecifies a pattern of n non-alphabetic characters, where n is an integer.

s-eNSpecifies a pattern of at least s and no more than e numeric characters, where s and e are integers and 0 < s ≤ e.

s-eASpecifies a pattern of at least s and no more than e alphabetic characters, where s and e are integers and 0 < s ≤ e.

s-eXSpecifies a pattern of at least s and no more than e characters, where s and e are integers and 0 < s ≤ e.

~~s-eNSpecifies a pattern of at least s and no more than e non-numeric characters, where s and e are integers and 0 <s≤e.

~~s-eASpecifies a pattern of at least s and no more than e non-alphabetic characters, where s and e are integers and 0 <s≤e.

If n is zero (0), the relation evaluates to true if zero or more characters in the expression string or substring match the specified character-type. A null string matches "0A", "0N", "0X", "..." and "".

Any characters in a pattern that do not correspond to any of these pattern fields are assumed to be literals. However, for clarity, this usage is discouraged in favour of explicitly quoted literals.

Note

The interpretation of these pattern fields depends on the setting of the EXT.MATCH compatibility option, as described below.

]A value mark (X'FD' or @VM, equivalent to CTRL+]).

Note

This behaviour can be changed with the MATCH.DELIM compatibility option.

Effect of the EXT.MATCH compatibility option

For the MATCH{ES} syntax to work exactly as described, including the newer pattern fields, the EXT.MATCH compatibility option must be set.

If EXT.MATCH is not set, only the original pattern fields are valid; that is: 'literal', nN, nA, and nX pattern fields work as described.

If EXT.MATCH is not set, any characters in a pattern that do not correspond to one of these four fields are assumed to be literals. The following table provides some examples.

If EXT.MATCH is set

If EXT.MATCH is not set

Example pattern

Matching expression

Equivalent pattern

Matching expression

"2A...1N"

DD9/773A-5

"2A'...'1N"

DD...5

"2A3-6N"

SS2341

"2A'3-'6N"

SS3-123456

"1X~~10-12A"

#0123456789

"1X'~~10-'12A"

#~~10-ACCELERATION

Examples

All the following examples assume that the EXT.MATCH compatibility option is set.

X = Z MATCHES '9N'

X evaluates to 1 if the current value of Z consists of 9 digits, otherwise it evaluates to 0.

X = B MATCH "3A'-'2N'-'4X"

X evaluates to 1 if B consists of three letters, followed by a hyphen, two digits, a hyphen, and four alphanumerics, otherwise it evaluates to 0.

X = A MATCHES "0N'.'0N"

X evaluates to 1 if the current value of A is any number containing a decimal point, or just a decimal point by itself; otherwise, it evaluates to 0.

X = V MATCHES "'£'1N','3N','3N"

X evaluates to 1 if V contains a string containing a pound sign followed by one digit, three digits and three digits again with commas separating them (for example, "£1,456,567"); otherwise it evaluates to 0.

X = Y MATCH ""

X evaluates to 1 if Y contains a null string; otherwise it evaluates to 0.

X = A MATCHES "1A4N"

X evaluates to 1 if the string value of A consists of one letter followed by four digit, otherwise it evaluates to 0.

X = Y MATCHES "1N3A]1N3A2N"

X evaluates to 1 if the string value of Y consists of one digit followed by three letters; to 2 if it consists of one digit followed by three letters and two more digits; otherwise it evaluates to 0.

MATCHFIELD function

The operation of MATCHFIELD function is similar to the MATCH{ES} operator except that when a match is found the substring of the expression that actually matched a specified pattern field is returned, optionally with any additional following fields. If the expression matches more than one pattern only the first matching pattern is used. If no match is found a null string is returned.

Syntax

MATCHFIELD( expression, pattern-phrase, start-field {, field-count} )

Syntax elements

expressionThe string expression to be matched.

pattern-phraseA string value that specifies one or more patterns, separated by value marks (although this behaviour can be changed with the MATCH.DELIM compatibility option). Each pattern can include any number and combination of the pattern fields as described for the MATCH{ES} operator.

start-fieldThe ordinal number of the pattern field to return from the first (or only) matching pattern in the pattern phrase.

The fields in a pattern are numbered starting from 1. If start-field is less than 1, it defaults to 1.

field-countThe (optional) maximum number of consecutive pattern fields to return.

If field-count is less than 1, or is not specified, it defaults to 1.

Operation

As with the MATCH{ES} operator the expression string is matched to the specified pattern or patterns in the pattern phrase.

If a match is found then the characters that match the specified pattern field or fields are returned. If there is no match then a null string is returned.

If start-field is greater than the number of pattern fields in the matching pattern then a null string is returned.

If the requested fields exceed the number of pattern fields in the matching pattern then the final returned string is limited by the end of the source string.

Examples

PATTERN = "2N'/'3-9A'/'4N"
DATE = "02/January/1977"
MONTH = MATCHFIELD(DATE, PATTERN, 3)

In this example the pattern phrase is a single pattern consisting of five pattern fields: 2N, '/', 3-9A, '/', and 4N; in other words: two digits, a slash, between three and nine letters, another slash, and four digits. So the DATE expression clearly matches the pattern. The MATCHFIELD function returns the third field of the pattern, so in this example MONTH would evaluate to "January".

OUTCODE = MATCHFIELD(POSTCODE, "1-2A1-2N' '1N2A]1-2A1N1A' '1N2A", 1, 3)

In this example the pattern phrase consists of two patterns. The first pattern has five pattern fields (1-2A, 1-2N, space, 1N, 2A) and the second pattern has six (1-2A, 1N, 1A, space, 1N, 2A). So, for example, if expression POSTCODE consists of "M60 1NW" it would match the first pattern, and if "EC1A 1BB" it would match the second. The MATCHFIELD function returns the first three fields of the first matching pattern, so in this example OUTCODE would evaluate to either "M60 " (note the trailing space character) or "EC1A".