>I am doing a select like this:
>
>SELECT * FROM TABLE WHERE ALLTRIM(TRCODE) = "103"
>
>and I get results that include empty TRCODE values (Character 6).
>If I check at the same time ?ALLTRIM(SPACE(6)) = "103" it returns .F. as I would expect.
>
>Why is the select picking up these records?
>
>following SET's are:
>SET EXACT ON
>SET ANSI OFF
>SET NEAR OFF

Craig,
'=' operator is somewhat different in SQL select. In SQL changing the left and right operands should yield the same result.

alltrim(trcode) where trcode is empty :
"103" = "" is true.
"" = "103" is also true since comparison ends as soon as the shorter one's chars end (SQL).

When making string comparison in SQL either set ANSI ON or use '==' operator. '==' also works different in SQL compared to when not in SQL.
In SQL it imlicitly makes the sizes of both operands same before comparison (unlike nonSQL comparison).

Below test demonstrates results with different settings + SQL vs nonSQL :

Create cursor myTest (myStr c(10))
For ix=1 to 100
  Insert into myTest values (trans(ix))
Endfor

Create cursor ExprTest (cExpression c(40), result l, setExact l, setAnsi l)

lcStr1 = 'space_after   '
lcStr2 = 'space_after'

cExpr1  = "lcStr1 = lcStr2"
cExpr2  = "lcStr2 = lcStr1"
cExpr3  = "lcStr1 = alltrim(lcStr2)"
cExpr4  = "alltrim(lcStr1) = lcStr2"
cExpr5  = "alltrim(lcStr1) = alltrim(lcStr2)"

cExpr6  = "lcStr1 == lcStr2"
cExpr7  = "lcStr2 == lcStr1"
cExpr8  = "lcStr1 == alltrim(lcStr2)"
cExpr9  = "alltrim(lcStr1) == lcStr2"
cExpr10 = "alltrim(lcStr1) == alltrim(lcStr2)"

Set exact off
Set ansi off
Test()

Set exact on
Set ansi off
Test()

Set exact on
Set ansi on
Test()

Set exact off
Set ansi on
Test()

Select ExprTest
Brow

Function Test
Local lcState, lcExpr

lcState = 'Exact is '+set('exact')+chr(13)+'Ansi is '+set('ansi')
For ix=1 to 10
  Store eval('cExpr'+trans(ix)) to lcExpr
  Insert into ExprTest ;
    values ;
    ( m.lcExpr, eval(m.lcExpr), (set('exact') = 'ON'), (set('ansi')= 'ON') )
Endfor
Wait window nowait lcState + chr(13) + 'With = operator'

Select * from myTest where myStr = '1'
Select * from myTest where alltrim(myStr) = '1'
Select * from myTest where '1' = myStr
Select * from myTest where '1' = alltrim(myStr)

Wait window nowait lcState + chr(13) + 'With == operator'

Select * from myTest where myStr == '1'
Select * from myTest where alltrim(myStr) == '1'
Select * from myTest where '1' == myStr
Select * from myTest where '1' == alltrim(myStr)

== behaviour in SQL makes it a nice Rusmore optimizable way to find empty reords. ie:
select * from myTable where empty(FirstName) && Empty() is not Rushmore opt.
select * from myTable where FirstName == '' && Rushmore opt.

Cetin