visual-foxpro Operators Relational operators

Example

Of all the operators, relational operators are the most complex ones, that is why we left them to the end.

Relational operators are also known as Comparison operators, they are used to compare things.

Comparison result is boolean false or true.

Interestingly though, if you check it in VFP help you only see a short list operations and a few more lines as if it is all about those operators.

Well, the complexity comes from the fact that, they operate on any types be it a numeric, date, datetime, logical or a string, and even on objects. Moreover, the behavior might look awkward, you don't get what you expect unless you know what effects the results.

Let's start with a list of relational operators:

Operator	Description	MOST Basic sample
>	Greater than	? 1 > 2 && .F.
<	Less than	? 1 < 2 && .T.
>=	Greater than or equal to	? 1 >= 2 && .F.
<=	Less than or equal to	? 1 <= 2 && .T.
=	Equal to	? 1 = 1 && .T.
==	Is exactly equal to (makes sense for strings)	? '1' = '1' && .T.
!=, #, <>	Not equal to (all 3 operators act the same way, choose your favorite)	? 1 != 1 && .F.

Although you can use these with all data types, there should be a type compatibility between the operands. For example, you would get an error if you try to compare a Date to an Integer.

Date and Datetime can be compared, although they are different types, VFP does the conversion implicitly for you.

? Date() > DateTime() && .F. 
? Date() <= DateTime() && .T. 
? Date() < DateTime() && .T. if it is not midnight

When the operands are numeric, all these operators are simple and straight forward, they work like they would do in mathematical expression.

With the logical operands, .F. is considered to be less than .T.

With objects what we are comparing is the reference of the object in memory. Thus the most used comparison is to determine if two object variables are pointing to the same object. ie:

local o1, o2
o1 = createobject('Label')
o2 = createobject('Label')
? m.o1 = m.o2 && is o1 and o2 the same object?
? m.o1 > m.o2 && this would work too but likely you would never use

* remember we are comparing their references in memory
* 
* They are different objects, but do they have any difference in their properties?
? CompObj(m.o1, m.o2) && .T. They are identical properties wise

Comparison of character data type, aka comparison of strings is the most confusing one in VFP. It doesn't work as in other languages and/or databases and unique to VFP (and maybe to some other xBase language).

Many years back, I have even seen some really advanced members in the community who weren't yet aware how these operators work in VFP. So it is quite understandable slight nuances might confuse the newbies easily.

Comparison is basically about being equal or not. If they are not equal, then we might think about the operators >, <, >=, <=, right? With strings it is confusing when two strings are considered equal.

Important: VFP strings are case sensitive. 'A' and 'a' are two distinct strings. This is not the case with many databases where the default is to use a case insensitive collation. For example in postgreSQL, or MS SQL Server on a table created with case insensitive (CI) collation:

select * from myTable where Country = 'TURKEY'

select * from myTable where Country = 'Turkey'

would yield the same result. In VFP though you only get those where casing matches. However VFP to has some collation support and makes case insensitive comparison. (Do not trust, see below)

• If two strings are not equal, so far so good, provided that you didn't change any defaults then they are compared based on their ASCII values.

  ? 'Basoz' < 'Cetin' && is true.
  ? 'basoz' < 'Cetin' && is false.
  ? 'Cetin' < 'David' && is true.
  ? 'Çetin' < 'David' && is false.
  

The default for collation is 'machine' and this is what you get then. When you change the collation to something else then you get the comparison based on that collation's sort order. With collation setting other than default machine you are also implying a case insensitivity on comparison ( do NOT trust this for equality ):

  set collate to 'GENERAL'
  ? 'Basoz' < 'Cetin'
  ? 'basoz' < 'Cetin'
  ? 'Cetin' < 'David'
  ? 'Çetin' < 'David'

Now all of these expressions are TRUE.

Personal advice: Collations in VFP has never been reliable enough. I suggest you not to use collations and stick with default 'MACHINE'. If you would use collations, then keep in mind to check it first when you experience something that is very unexpected regarding character data. I have seen and demonstrated that it fails in many cases, but then I stopped trying to use it much before VFP9 version, it might be consistent now, I really don't know.

Considering we covered inequality cases with strings, the tricky one is the equality case. In VFP basically two settings effect the comparison:

1. SET EXACT (Default is OFF and effects regular comparison's - those except SQL)
2. SET ANSI (Default is OFF and effects comparisons in SQL only. SET EXACT has no effect on comparisons made within SQL queries.

With SET EXACT OFF, read the comparison as "does string at right start with the string at left"? They are compared up to the right string's length.

? "Bobby" = "B" && Bobby starts with B, so TRUE
? "Bobby" = "Bob" && Bobby starts with Bob, so TRUE
? "Bobby" = "Bob " && Bobby starts with Bob but there is a trailing space there, FALSE
? "Bobby" = "bob" && would be true with collation set to GENERAL

Note that with regular comparison, "Bobby" = "B" is TRUE, but "B" = "Bobby" is FALSE. In other words, the place of operands are important.

With SET EXACT ON the strings must match fully but their trailing spaces are ignored (we are ignoring set collate here which would also do case insensitivity):

? "BOBBY" = "BOB" && FALSE 
? "BOBBY" = "BOBBY" && TRUE
? "BOBBY" = "BOBBY     " && TRUE 
? "BOBBY     " = "BOBBY" && TRUE

Now, with SQL commands SET EXACT has no effect and it would behave like SET EXACT OFF does.

Select * from Customers where Country = 'U'

Would select the customers from USA, UK any country beginning with 'U'.

In SQL, however, by definition changing the order of operands should yield the same result. Thus:

Select * from Customers where 'U' = Country

would also work the same way (note the difference from non-SQL commands).

When you want to imply exact matches, one option is to turn on ANSI:

SET ANSI ON
Select * from Customers where Country = 'USA'

returns all those customers from USA. Note that, the trailing spaces in country field OR on the right expression is ignored. It wouldn't matter how many trailing on either side you have. You get the comparison as if it were done like: RTRIM(Country) = RTRIM('USA').

Although it is not mentioned in Operators in VFP, an SQL operator is LIKE. When you use LIKE, you get an exact match comparison regardless of SET ANSI setting (using LIKE forces and implicit ANSI ON case - it is an ANSI operator after all). However, beware there is a slight difference in behavior. It wouldn't ignore trailing spaces, unless the total size with trailers is equal to or less than field size. For example if Country field is C(10), then Country = 'USA' or Country = 'USA__' would work, but Country = 'USA___________' would fail (underscores denote a space and last one has more then 7 trailing spaces).

At last we are up to the last operator, ==. That means exactly equal and makes to use with strings. One advantage is that, using == you always mean that you want exact match regardless of SET EXACT or SET ANSI settings. However beware again, its behavior is different when it is an SQL command or nonSQL regular command.

With SQL:

Select * from Customers where Country == 'USA'

whatever the ANSI and EXACT settings are, we want all the customers from USA only. Trailing spaces on either side is ignored.

With Non-SQL:

? m.lcString1 == m.lcString2

would be true only if they are exactly same, regarding their casing and length (trailing spaces are NOT ignored). It is not effected from SET ANSI, EXACT or COLLATE settings.