SET ANSI, SET EXACT

These two commands do almost the same thing, but in different situations. They both control the way string comparisons are performed. SET ANSI affects SQL commands; SET EXACT affects Xbase commands.

Usage

SET ANSI ON | OFF
SET EXACT ON | OFF

Xbase started life as an interpreted language whose commands were meant to be entered interactively. In that situation, it was handy to be able to type something like SEEK “SM” and move the record pointer to the first “SMITH” (or whatever happened to be the first record starting with “SM”). It was especially convenient to be able to do similar things in FOR clauses, so you could list all the people whose names started with “S” or all the parts whose part numbers began with “124”.

To make those kinds of things possible, the default behavior of the “=” operator is to compare strings only until the end of the string on the right-hand side. That is, in Xbase, “123”=”12” and “Smithsonian”=”Smith”. But “Smith”<>“Smithsonian”. Pretty weird.

SET EXACT controls this behavior. When it’s OFF (the default), you get the partial comparisons. When EXACT is ON, strings have to match exactly. You can also use the “==” operator to test for exact equality. (In fact, we read “==” as “exactly equals.”)

Once upon a time, the double equal sign operator wasn’t optimizable, but it has been for a lot of versions already. So, we prefer to leave EXACT OFF and use the double equal sign because it’s a local solution to a local problem.

SET ANSI, which also defaults to OFF, is almost the same. It affects FoxPro’s SQL commands: SELECT, DELETE-SQL and UPDATE-SQL. There’s one subtle difference. With SET EXACT OFF, it matters which side of the “=” is shorter. With SET ANSI OFF, it doesn’t matter. The strings are compared to the end of the shorter string, whichever side it’s on. So, in SQL commands, “Smithsonian”=”Smith” and “Smith”=”Smithsonian.”

The exactly equals operator isn’t as strict in SQL commands as it is in Xbase commands. Regardless of the setting of ANSI, in a SQL command, == ignores trailing blanks.

One final note: Keep in mind that the “not equals” operators, “<>” and “#”, are also affected by SET EXACT and SET ANSI.

Both SET EXACT and SET ANSI are scoped to the data session.

Example

USE Customer
SET EXACT OFF                 && The default
BROWSE FOR Company_Name = "G" && You get all the companies
                              && that start with "G"
SET EXACT ON
BROWSE FOR Company_Name = "G" && Empty Browse

See Also

Delete-SQL, Select-SQL, Update-SQL