SET ANSI Command

Specifies whether to pad a shorter string with spaces when making a SQL string comparison or binary expression with zero (0) bytes when making a binary expression comparison in SQL commands using the equal sign operator (=).

SET ANSI ON | OFF

Parameters

• ON
  Pads the shorter string or binary expression with spaces or zero (0) bytes needed, respectively, to make it equal to the length of the longer string or expression. When SET ANSI is set to ON, the two strings or expressions are compared character for character for their entire lengths.

• OFF
  Specifies that the shorter string not be padded with spaces or binary expression not be padded with zero (0) bytes. (Default)

  When SET ANSI is set to OFF, the two strings are compared character for character until the end of the shorter string is reached.

Remarks

SET ANSI has no effect on the double equal sign (==) operator. When you use the == operator, the shorter string or binary expression is always padded with spaces or zero (0) bytes, respectively, for the comparison. For more information, see Relational Operators.

SET ANSI is scoped to the current data session.

SET ANSI and the Query Designer   Visual FoxPro builds a SELECT - SQL command in the Query Designer when you create a query. When creating Join and Filter conditions, if you choose the Equal or Exactly Like options, the = or == operator is included in the generated SELECT. The SET ANSI setting can affect the results of queries you create and perform in the Query Designer.

String Order   In SQL commands, the left-to-right order of the two strings in a comparison is irrelevant — switching a string from one side of the = or == operator to the other doesn't affect the result of the comparison.

Example

The following example creates a cursor with one record and then demonstrates how SET ANSI affects query results. When SET ANSI is set to OFF, the result is one because the strings are compared based on the shortest. 'Tommy' becomes 'Tom', and the strings 'Tom' and 'Tom' do match character for character.

When SET ANSI is set to ON, the result is zero because the strings are compared based on the shortest padded with spaces. 'Tom' becomes 'Tom   ', and the strings 'Tom   ' and 'Tommy' do not match character for character:

CLEAR
CREATE CURSOR Dummy (name c(10))
INSERT INTO Dummy VALUES ("Tommy")

SET ANSI OFF
SELECT * FROM Dummy WHERE name="Tom" INTO CURSOR result
?"With ANSI",SET("Ansi"),_tally     && shows 1

SET ANSI ON
SELECT * FROM Dummy WHERE name="Tom" INTO CURSOR result
?"With ANSI",SET("Ansi"),_tally     && shows 0

See Also

Reference

String and Value Comparison in Visual FoxPro
SET EXACT Command
SET Command Overview