Subject Re: [firebird-support] How to create a string column with significant trailing spaces
Author Ivan Prenosil
> There is a difference in behaviour between firebird and most other RDBMS
> regarding string columns.
> It seems like firebird ALWAYS ignores trailing spaces in string fields
> when matching,
> while other vendors distinguish between CHAR and VARCHAR fields here,
> where VARCHAR fields are not padded automatically.

Firebird just conforms to SQL standard, probably most other RDBMS ignore it.

> I have a usecase where I would like to declare a database column without
> padding,
> i.e. the selections "WHERE col = 'xyz' " does NOT deliver rows with col
> = 'xyz ',
> while otherwise preserving the normal collation properties , like
> sorting order and similar.

It is standard/correct/expected behaviour. There are several ways how to make
search trailing-spaces sensitive:

- use "LIKE" operator instead of "="

- use padding character different from your space. Character set OCTETS
is padded by binary zero, not by space, so try this

WHERE col = _OCTETS 'xyz'

- use Firebird 2 where you can define collations that do not pad data when comparing