This is caused by the setting of SET ANSI. The SELECT ... WHERE command is affected by SET ANSI whereas COPY TO ... FOR is not.

If you issue SET ANSI ON, they will return the same results, but my opinion is to leave the ANSI OFF (default) and not use ALLTRIM in your SELECT.

Bo Durban


> Hi.
> if I do the following:
> Select * from Mfile where Alltrim(Myfield) = 'Any Data'
> ... this returns all records with a blank in Myfield
>
> However Copy to temp for Alltrim(Myfield) = 'Any Data' return the records > correctly.
>
> Anybody know why this happens
>
> If I use Myfield instead, (without the alltrim)it works fine
> I've tried it with set exact on and off and it does not matter.