--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "phil_hhn" <time_lord@>
> wrote:
> >
> > Hi, I'm currently using Firebird 1.5.3.
> >
> > I have a column called 'MyText', varchar(100). This column may
> contain
> > works with accents, eg "hát" (the second character has an accent -
> it
> > is U 00E1). For searching purposes, I need to be able to find this
> > word regardless of case. Always performing an 'UPPER()' on this
> column
> > is slow (there are a lot of rows), so for performance I added
> another
> > column 'MyUText'.
> >
> > In IBExpert I ran 'update MyTable set MyUText = upper(MyText)', but
> > this does not convert all characters; in the above example "hát"
> > becomes "HáT" (the second character was not uppercased).
> >
> > 1) Is it possible for firebird to 'upper' characters with accents
> > also? Do I need to change the way the MyUText column is defined (eg
> a
> > particular codeset)? I accepted all defaults, locales, codesets, etc
> > when I first created the database.
> >
> > 2) Is it possible for firebird to convert accented characters to
> > non-accented ones (where an equivalent exists)? Then for example I
> > could search for "HAT" and find "hát" (where the equivalent -
> > uppercased - was "HAT").
> >
> > 3) I need to add a trigger so that whenever MyText is modified (or a
> > row added) then MyUText is updated. This may only be practical if
> (1)
> > is possible; otherwise, I'll have to do the conversion through my
> java
> > code. Since MyText is updated from many different areas, a trigger
> > would be preferable.
> >
> > Any comments? Are there any enhancements in Firebird v2 that are
> > relevant here?
>
> Read about COLLATIONS.
>
> Firebird 2 will introduce the ability to index an expression, such as
> UPPER(MyText), but once you read about collations and figure out the
> best one to use, it may not be relevant.
>
> Adam
>

Thanks Adam,
I thought it may have been to do with codesets... much easier than I
was expecting. I changed the charset and collation and UPPER(MyText)
now works correctly without problem, great.
However I've been unable to achieve (2) - I thought maybe I could set
the charset/collation of MyUText column to something like ASCII, but
calling 'set MyUText = upper(MyText)' fails with an
overflow/truncation - no surprise I guess. Do you think some variation
on this approach would work or is this never going to work?

Thanks,
Phil