Estie escreveu:

> --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
> wrote:
>>> Hi. We have a table with applicants and use the 'select distinct
>>> (surname) from applicant' to populate a drop-down.
>>>
>>> The resulting list includes applicant with a surname "Wilson " but
>>> not "Wilson".
>>>
>>> Any ideas about why this could be? We are using Firebird 2.1 on
> Windows
>>> 2003 Server.
>>> Regards, Pieter
>> Is the (surname) field a varchar? or char? It might be char in your
> case and
>> maybe it should be varchar.
>> How are the values inserted or updated? via your own interface or
> via an
>> external datapump of some kind?
>> Alan
>>
> The (surname) field is varchar(20) using win1252 characterset and
> collation. The values are entered manually by the applicant or
> interviewer.
>
> How does the distinct verb decide whether a value is distinct in a
> list or not?
>

Accordingly to SQL standard, by default 'Wilson' = 'Wilson '. You could
create and use a NO PAD collation since v2.0.

create collation win1252_nopad -- you could change this name
for win1252
from win1252 no pad;

Using this collation 'Wilson' will be <> 'Wilson ' and hence they will
be distinct.

Please note that this feature was not widely tested.


Adriano