Subject Re: [firebird-support] How to create a string column with significant trailing spaces
Author Thekla Damaschke
Ann W. Harrison wrote:
> 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.

That is not really true, the standard defines a property
"PAD SPACE" or "NO PAD" for collations, where
/Under a NO PAD collation, a string s1 that is shorter than some other
string s2,
but "compares equal" to that leading substring of s2 that is the same
length as s1,
is considered to be less than s2 even if all remaining characters of s2
are spaces./

> 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'.

The bad thing with LIKE queries is,
they are rarly able to make use of indexes on the string columns.
Is firebird here so much different from other RDBMS?

Ok, I will subscribe to the devel list and ask my question there again,
Ivan says I could do this in Firebird2 and I did not find information on

Thanks anyway
Thekla Damaschke