Hi Panagiotis,
I CC'ed this response to the support list, as it
may be of some help in the archives there.

=> firebird-support benevolent dictators:
I've made a fullquote to have the context

=> firebird-devel benevolent dictators:
(if any is reading this)
I've found this thread to be somewhat more on
topic as the usual support question in devel,
as it touches defiencies in current FB INTL
implementation.

O.K. back to the factual topic:

> I want to be able to make insensitive, accent
> insensitive string compares.

Panagiotios, so I think you didn't look
for ORDER BY being no-case/no-accent
but more in the direction of having a
no-case/no-accent index. So you can
try the proxy column or the BETWEEN
method.

Regards,
Peter Jacobi

Thread history:
Panagiotis <giotismail@...> wrote:

> Thank you very much for the support.
>
> I am trying to find a free SQL server for windows in
> order to replace MS SQL Server.
>
> I want to be able to make insensitive, accent
> insensitive string compares.
>
> I will follow your advice and see if that works.
>
> Thank you again,
> Panagiotis
>
> --- Peter Jacobi <pj@...> à î: >
> Hi Panagiotis,
> >
> > > I created a database with win1253 character set
> > and collation and i created
> > > a table named table1, with only one field that is
> > called field1
> > > (varchar(100))
> > >
> > > When i execute
> > > SELECT UPPER(field1) FROM table1
> > > the US characters are correctly converted to
> > uppercase but the Greek remain
> > > lowercase.
> >
> > The character sets in their default collation are
> > pretty dumb. If you want
> > correct uppercasing and collate behaviour, you must
> > specify a language
> > specific collation in addition:
> >
> > create table1 (field1 char(16) character set WIN1253
> > collate PXW_GREEK);
> >
> > As you can give a default character set for a
> > database, but not a default
> > collation you must do this field by field.
> >
> > It should be possible to create derived character
> > sets with a specific
> > collation, using syntax like
> > create character set WIN1253_GREEK as WIN1253
> > collate PXW_GREEK;
> > But this is not implemented yet.
> >
> > > Another thing is the ORDER BY. On SQL Server i can
> > ask to have accent
> > > insensitive and case insensitive ORDER BY with
> > Greek characters.
> > > Can i enable a feature like this in firebird?
> >
> > I don't really see the use case for
> > no-case/no-accent ORDER BY.
> >
> > If you don't care about the specific order of
> > strings differing
> > only by casing or accents, why is the order returned
> > by a
> > multi-level collation like PXW_GREEK not OK?
> >
> > Using a latin script example, and the strings:
> >
> > AAA
> > aaB
> > AaB
> > AAB
> > AAC
> >
> > This is the order PXW_GREEK will sort the strings. A
> > no-case order will
> > either return this sequence or one of the other five
> > generated by
> > permuting the middle three entries. So it is hard to
> > see, which requirement
> > is addressed by this behaviour.
> >
> > If you are really after no-case/no-accent queries,
> > things become
> > more interesting (in the worst sense of this word):
> >
> > You can get no-case action by creating a 'proxy
> > column' storing
> > UPPER(field1), but there is no NOACCENT yet.
> >
> > You can get extra no-case and no-accent collation
> > from
> > http://www.brookstonesystems.com/
> >
> > For no-case you can use the fact that the CONTAINING
> > operator works case insensitive.
> >
> > For no-case/no-accent you can sometimes you
> > following
> > reformulation of your query
> > Let '!' be a character sorting before are the ones
> > you are
> > interested in and ' ' a character sorting behind all
> > these.
> >
> > Then the query
> > BETWEEN 'abc' AND 'abc!'
> > will match the fields equalling no-case/no-accent
> > 'abc'.
> >
> > Anbd the query
> > BETWEEN 'abc' AND 'abc '
> > will match the fields starting (no-case/no-accent)
> > with 'abc'.
> >
> > Best Regards,
> > Peter Jacobi
> > Hamburg, Germany
> >

--
NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien...
Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService

Jetzt kostenlos anmelden unter http://www.gmx.net

+++ GMX - die erste Adresse für Mail, Message, More! +++