So there is no alternative collate that will sort correctly on spaces
etc? The problem is that this a user field to define a sort sequence
in a contacts database. The user wants to sort by this field
specifically, case insensitively and respecting the sequence on
foreign accented characters where they exist. Is there any way of
doing this?

Jonathan Hyams

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

>
> artlooksoftware wrote:
> > My database is set to use ISO8859_1. With the following sql select
> > statement
> > select * from ATABLE order by AFIELD collate en_uk
> > the sorted fields are somewhat erratic. For example:
> >
> > Brown, Douglas
> > Browne, David
> > Brown, Fiona
> >
> > Is this a bug or am I misunderstanding something?
>
> I'm afraid you're misunderstanding something. In the EN_UK collation,
> commas and spaces have only secondary significance - 'a, b, c' sorts at
> the same level as 'abc' and before 'abd'
>
>
> SQL> insert into t1 values ('abc');
> SQL> insert into t1 values ('abd');
> SQL> insert into t1 values ('a b c');
> SQL> insert into t2 values ('a, b, c')'
> SQL> insert into t1 values ('a, b, c');
> SQL> insert into t1 values ('a, b, b');
> SQL> insert into t1 values ('abb');
> SQL> select * from t1 order by f1;
>
> F1
> ==================================================
>
> abb
> a, b, b
> abc
> a b c
> a, b, c
>
>
> The ordering you're seeing makes sense - more or less - if you thing of
> the data as BrownDouglas, BrowneDavid, BrownFiona. The correct answer
> is to separate names inot their components and sort them separately.
> E.g. select last_name || ', ' || first_name from names order by
> last_name, first_name.
>
> Regards,
>
>
> Ann
>