Fabio,
See my comments inline
>Sorry, my title is not exact.
>Exact is:
>"PROBLEM: i want use == on comparison of strings"
>
>If i define on SQL Server a field F1 CHAR(2) and insert a string like 'A ',
>SQL Server not found 'A'.
>Example:
>>CREATE TABLE #mytable (f1 char(2) )
>INSERT INTO #mytable VALUES ('A ')
>SELECT * FROM #mytable WHERE f1='A'
>DROP TABLE #mytable
>>
>I supposed that in SELECT command, SQL Server type CHAR(nn) he was much similar one to VFP type C(nn), but is not true.
>
>On VFP right blank spaces on character field is ignored on .
>>CREATE CURSOR myCur (f1 C(2))
>INSERT INTO myCur VALUES ('A ')
>SELECT * FROM myCur WHERE f1=='A'
>COUNT FOR f1=='A'
>? _TALLY
>...
>This behavior is explained in
SET ANSI Command topic in the help:
"ON
Pads the shorter string with the blanks needed to make it equal to the longer string's length.
...
The == operator uses this method for comparisons in Visual FoxPro SQL commands".
>Then, for VFP C(nn) fields, use RTRIM(fieldName) is useless on :
>- INDEX with only a field.
>- WHILE and FOR with only a field.
>- WHERE with only a field.
>
>REASON: Yes it explanation has one, because
>>INSERT INTO #mytable VALUES ('A ')
>>or
>>INSERT INTO #mytable VALUES ('A')
>>is identical.
>
>But another problem is for LOOKUP()
>>SET EXACT OFF
>? LOOKUP(F1,'A'+SPACE(1000),F1)
>* for set soft you get a hard comparison
>SET EXACT on
>? LOOKUP(F1,'A'+SPACE(1000),F1)
>* for set hard you get a soft comparison
>
>* but if you define a INDEX
>INDEX ON f1 tag TAG1
>? LOOKUP(F1,'A'+SPACE(1000),F1)
>
>and for this,REASON: is not valid.
>
>Attention on:
>>DO WHILE !EOF() AND f1=='A'
> ...
> SKIP
>ENDDO
>
>and
>
>SCAN FOR f1=='A'
>....
>ENDSCAN
>
>are not equal.
>
>To notice that VFP manipulates also the PADR()
>>SELECT * FROM myCur WHERE PADR(f1,2)=='A'
>
>
>But then VFP remove any string space on the SELECT, also for the string literal and variables.VFP doesn't remove spaces. See excerpt from the help file above.
>>SELECT * FROM myCur WHERE 'A '=='A'
>
>SELECT * FROM myCur WHERE 'A'+SPACE(RAND(0)*1000)=='A'+SPACE(RAND(0)*1000)
>
>v1='A '
>v2='A'
>SELECT * FROM myCur WHERE m.v1==m.v2 AND m.v2==m.v1
>
>Yes, it is true, before any string comparison VFP RTRIM() any string.No it isn't true. See excerpt from the help file above.
>
>Strong problem:
>This problem occur also on MEMO fields, BINARY
>
>If i put binary data on a Memo(binary), i cannot compare substrings of
>this field on reliable mode.
>
>A workaround is:
>>SELECT * FROM myCur WHERE f1+CHR(0)=='A'+CHR(0)
>
>but then for Rushmore speed up you must define f1+CHR(0) on index expression,
>and use this expression on any comparison.
>
>Hovewer, for me this is a problem, also without SQL Server backend using.
>
>You pardon to me if what I have written it is all mistaking.
You may consider using LIKE SQL Select operator which starting with VFP7 respects trailing spaces in the character fields.
--sb--
