String comparison in VFP

Submitted by Sergey on January 2, 2008 - 02:14 UTC
topic: 
vfp
foxpro
comparison

FoxPro has two distinct parts. The SQL Data Engine that handles SQL command: SELECT, INSERT, UPDATE and DELETE and the rest that handles procedural code. There are differences in string comparison between them. The SET ANSI command controls comparison in the SQL Data Engine and the SET EXACT does the same in the procedural code.

SET ANSI OFF

- SQL Data Engine (default setting)
The strings are compared to the length of the shortest string. As result, the comparison will return true if one of the strings is zero-length string regardless of the other string content.

SET EXACT OFF

- default setting
The strings are compared to the length of the string on the right of equal sign. As result, the comparison will return true if string on the right is zero-length string regardless of the other string content.

SET ANSI ON

- SQL Data Engine
The shorter string is padded to the length of the longer one before comparison and then compared character for character to their entire lengths.

SET EXACT ON


The shorter string is padded to the length of the longer one before comparison and then compared character for character to their entire lengths.

== Operator The SET EXACT and SET ANSI have no affect on ==

operator. In the SQL Data Engine it produces the same result as SET ANSI ON but could be slower. In the procedural code strings must contain exactly the same characters, including blanks, to be considered equal.

The sample code demonstrates some of the points discussed.


$SAMPLECODE$





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91





CLEAR

CREATE TABLE TestTable (text1 C(10), text2 C(5))
INSERT INTO TestTable VALUES ("One", "One")
INSERT INTO TestTable VALUES ("Two", "Two")
INSERT INTO TestTable VALUES ("Three", "Three")
* Zero-length string
lc0LenStr = ""

SET EXACT OFF
? "SET EXACT is "+ SET("EXACT")
? "Comparison is to the length of the right side string"
? "One  "      = "On"         && .T.
? "One"        = "On  "       && .F.
? "One  "      = "One"        && .T.
? "One"        = "One  "      && .F.
? "One"        = lc0LenStr    && .T.
? lc0LenStr    = "One"        && .F.
?

SET ANSI OFF
? "SET ANSI is "+ SET("ANSI")
? "Comparison is to the length of the shortest string"
SELECT * FROM TestTable WHERE text2 = "On" INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE "On" = text2 INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE text2 = "One" INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE "One" = text2 INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE text2 = lc0LenStr INTO CURSOR crsResulr
? _TALLY        && all 3 record matches
SELECT * FROM TestTable WHERE lc0LenStr = text2 INTO CURSOR crsResulr
? _TALLY        && all 3 record matches
?

SET EXACT ON
? "SET EXACT is "+ SET("EXACT")
? "The shorter string is padded with spaces before comparison"
? "One  "      = "On"         && .F.
? "On"         = "One  "      && .F.
? "One  "      = "One"        && .T.
? "One"        = "One  "      && .T.
? "One"        = lc0LenStr    && .F.
? lc0LenStr    = "One"        && .F.
?

SET ANSI ON
? "SET ANSI is "+ SET("ANSI")
? "The shorter string is padded with spaces before comparison"
SELECT * FROM TestTable WHERE text2 = "On" INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE "On"  = text2 INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE text2 = "One" INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE "One" = text2 INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE text2 = lc0LenStr INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE lc0LenStr = text2 INTO CURSOR crsResulr
? _TALLY        && 0 record matches
?

? "== Operator. The string are compared character by character"
? "One  "     == "On"         && .F.
? "On"        == "One  "      && .F.
? "One  "     == "One"        && .F.
? "One"       == "One  "      && .F.
? "One"       == lc0LenStr    && .F.
? lc0LenStr   == "One"        && .F.
? "One"       == "One"        && .T.
?

? "== Operator. The shorter string is padded with spaces before comparison"
SELECT * FROM TestTable WHERE text2 == "On" INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE "On"  == text2 INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE text2 == "One" INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE "One" == text2 INTO CURSOR crsResulr
? _TALLY        && 1 record matches
SELECT * FROM TestTable WHERE text2 == lc0LenStr INTO CURSOR crsResulr
? _TALLY        && 0 record matches
SELECT * FROM TestTable WHERE lc0LenStr == text2 INTO CURSOR crsResulr
? _TALLY        && 0 record matches

Comments