I’m very new to Base but have experience in MS Access so I’m trying out various things. I have come across this odd behaviour with a Select Query where there are Wildcards used.
Assume I have a Text field of Type CHAR (not VARCHAR) of length 100 containing John Evans. A select Query with LIKE being ‘John Evans’ finds the record as does ‘John%’. However with LIKE set to ‘%Evans’ the record is not found. It is necessary to set LIKE to ‘%Evans%’ to find the record. It is even worse with the single character wildcard . If this set anywhere such as 'John Evan’ again the record is not found and has to be set to ‘John Evan_%’ to find the record.
If the field is set to type VARCHAR then it all works as expected without having to put the anything Wildcard % at the end of the LIKE criteria.
I am wondering if this is caused by the fixed length Type CHAR being padded out to the fixed length and the % is required to match the padding, although if this were the case then the LIKE set to ‘John Evans’ without a Wildcard would have to be ‘John Evans%’ which is not the case. It seems to occur only when a Wildcard is introduced.
Perhaps this is a reason not to use CHAR but to use VARCHAR!!