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 (=).
|In the Visual FoxPro OLE DB Provider, you cannot query the value of ANSI using the SET command though SET ANSI is supported.|
SET ANSI ON | OFF
- 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.
- 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.
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.
In the following example, when SET ANSI is set to ON, the result is False (.F.) because when padded, 'Tom' becomes 'Tom ', and the strings 'Tom ' and 'Tommy' do not match character for character:
'Tommy' = 'Tom'
The == operator uses this method for comparisons in Visual FoxPro SQL commands.
In the following example, when SET ANSI is set to ON, the result is True (.T.) because the comparison stops after 'Tom':
'Tommy' = 'Tom'