Odd behaviour in Base Query with Wildcards

asked 2015-06-22 15:53:13 +0100

peterwt gravatar image

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!!

edit retag flag offensive close merge delete

Comments

yes, I think that is the reason, that with a CHAR(100) the entry is always padded to the end, which also makes it inefficient for storing variable-length strings. Every string is 100 characters no matter how little you type. Looks like some databases automatically trim the trailing spaces for retrieval, apparently HSQLDB does not. Only use char for very short, fixed-length strings.

doug gravatar imagedoug ( 2015-07-01 13:46:45 +0100 )edit