Subject Re: [firebird-support] How to create a string column with significant trailing spaces
Author Ann W. Harrison
Thekla Damaschke wrote:
> I can't use a special syntax in the queries, because the same program
> also runs against
> other RDBMS, so I would like to try to define a collation without padding.

That's not going to work. The rules about the significance of
trailing blanks are in the engine, not in the collation handling.

The standard is quite clear that for equality comparisons of
strings - whether char or varchar - the shorter operand is
extended with blanks to the length of the longer. Thus
'xyz' = 'xyz ' will always be true, regardless of collation
and whether they are char, varchar, or mixed.

The standard rules for LIKE, on the other hand, do consider
trailing blanks significant, so 'xyz' LIKE 'xyz ' will always
be false, regardless of collation and character type. If your
field is char [5] and you store 'xyz' it will be extended with
blanks, and will fail a like comparison with 'xyz'.