Any comment?

When Comparing Character Strings Doesn’t!

[1] In Native VFP Code
At first glance comparing two strings is simple. After all, it is fundamental to all programming that we should be able to test two values and, depending on the result, take the appropriate action. When dealing with numeric data, logical fields and dates, there is little problem because the data is unambiguous. However, when we start trying to compare character data, things get a little more difficult. We have all written code like this:

IF lcCategory = ‘A’ OR IF lcChoice = lcDefaultAction
*** Do this *** DO this
ELSE ELSE
*** Do that *** DO that
ENDIF ENDIF

So how does Visual FoxPro decide whether the value which is pointed to by ‘lcCategory’ is actually equal to “A” or that lcChoice is equal to lcDefaultAction? The answer is, of course, that “it depends!”. The table below shows what happens when a simple comparison to a constant value is executed when SET( ‘EXACT’ ) = ‘OFF’:

    Value       Compare To        Result
(lcCategory)    (Constant)     (Using “=”)	
      A	            A	          True
      A	            a	          False
      A	           “”	          True
      a	            A	          False
      a	           “”	          True

Well, the first two seem obvious enough, and so is the fourth, but what about number three and number five? What this seems to be telling us is that:

“A # a” and “a # A” but since both A = ‘’ AND a = ‘’ then logically “A = a”

After all it is axiomatic that two values which are equal to the same value must also be equal to each other! The answer to this apparent paradox lies in the way in which Visual FoxPro compares two strings. What is actually does is to take the string on the RIGHT of the equal sign and then compare the string on the LEFT character by character until the end of the string on the right is reached. If there have been no differences, the two strings are considered equal and the comparison returns TRUE. If there is nothing on the right of the comparison, there is nothing to compare the string on the left with. The result, because there has been no comparison at all is that there are no differences, so we get a return value of true. What it actually means is that no comparison was done at all!
So what happens if we swap the expression around so that the constant is on the left and our test value on the right? It now behaves exactly as we would expect.

    Value       Compare To        Result
(lcCategory)    (Constant)     (Using “=”)	
     A	           A	           True
     a	           A	           False
    “”	           A	           False
     A	           a	           False
    “”	           a	           False

This behavior has puzzled many people over time, but it is actually quite logical when you remember that the strings are compared on the basis of length. So long as the string on the left matches the string on the right character by character the result is always true. “ABC” IS equal to “A” to Visual FoxPro. So when you are comparing a constant to a variable you can avoid the issue by placing the constant on the left of the expression. However, when comparing two variables you may not know whether one of them is actually an empty string and so cannot simply rely on having the expression ordered correctly!
If that were the end of the matter, it would be a very serious problem, fortunately it isn’t. There are two ways of resolving such comparisons properly. The first is to change the setting of “EXACT”. This setting, which is OFF by default, defines how Visual FoxPro compares two strings. By setting EXACT = ON we should be able to force Visual FoxPro to only accept a comparison as true when every character on the left hand side is matched exactly to the corresponding character on the right. However when we test it:

? “Does this match?” = “Does this match?” && TRUE
? “Does this match? ” = “Does this match?” && TRUE
? “Does this match?” = “Does this match? ” && TRUE

We still have a problem! This is because what SET EXACT actually does is to TRIM both expressions and compares the results. So we will now get return of true whenever the strings match except for trailing blanks! It seems to me that SET EXACT should really be named “SET ALMOSTEXACT”!
SET EXACT ON is not a good solution for two reasons. Firstly, because it is using a global setting to resolve a local problem. (Unless you actually test the current setting of EXACT every time you want to use a string comparison you can never be sure how Visual FoxPro will react!) Secondly, and this is the real killer, there are occasions when the trailing blanks can be important and using EXACT makes it impossible to include them.
The second, and much better solution, is to use the “==” (double equal, or ‘Exactly Equal’) operator. This tells Visual FoxPro that we really DO want to know if the true strings are exactly the same in all respects. Now, finally, we get the correct results in all situations.

? “Does this match?” == “Does this match?” && TRUE
? “Does this match? ” == “Does this match?” && FALSE
? “Does this match?” == “Does this match? ” && FALSE

[2] In SQL Statements
The rules in SQL are slightly different from those that apply in standard Visual FoxPro code. Firstly, it does not matter on which side of the “=” sign an expression occurs, the result is always dependent upon comparing the shorter string to the longer. Thus:

SELECT * ;
FROM customers ;
WHERE cust_id = “AL”

is always interpreted as ‘Select customers whose identifiers BEGIN WITH ‘AL’” and so this type of WHERE clause is effectively equivalent to writing:

WHERE cust_id = “AL%”

Secondly SET EXACT has no effect whatsoever on SQL code. But SET ANSI behaves in exactly the same way. With ANSI OFF, "=" means "one of these strings starts with the other". With ANSI ON, "=" means "is equal to except for trailing blanks".

Finally even the “==” operator will not work in exactly the same way in SQL. It actually behaves the same as the SET ANSI setting, and so will still ignore trailing blanks when comparing character strings.

In fact the only way to absolutely ensure that two character strings, including trailing blanks, are equal is to force an additional, non-space, character on to the end of each string that you want to compare so as to ensure that there no trailing blanks.
[3] Testing for negatives
The same problems arise when testing for negatives using any of the ‘not equal’ operators (“#”, “<>” or “!=”). These comparisons are also affected by SET EXACT. To see how it works, you have to turn it inside out. First the two strings are checked to see if they're equal (or what passes for equal with SET EXACT OFF). Then, the result is reversed. So, with SET EXACT OFF:

? "Forgotten"<>"For" && FALSE

While with SET EXACT ON,

? "Forgotten"<>"For" && TRUE

But I have already said that SET EXACT should not be used! So how can you check inequality without it? The trick is to do manually what Visual FoxPro is doing automatically. In other words, do the equality comparison first, then reverse it. To check for "exact inequality", use:

? NOT( "Forgotten" == "For") && TRUE