Is this not possible? To get correct sorting with UTF8 database and Norwegian chars?

I'm using PHP 5.2 and firebird 2.0.

Have created a database like this:

CREATE DATABASE 'localhost:c:/ibase/ONFIRE.FDB' user 'SYSDBA' password 'secret' DEFAULT CHARACTER SET UTF8;

So when I create the character fields in database their charset are UTF8.

Then I insert some records, some with the chars æøå and some without.

The records are in the table and they are displaying correct in HTML.

But when I want to sort the the content in the table like this:

select PAG_ID,PAG_NAME from pager where lower(PAG_NAME) like 'a%' order by lower(PAG_NAME) collate UCS_BASIC

or like this

select PAG_ID,PAG_NAME from pager where lower(PAG_NAME) like 'a%' order by lower(PAG_NAME) collate UNICODE

the sort order for the records containing æøå is not correct:
It is like this:
Aå cool site
Aæ cool site
Aø cool site

But should have been like this:
Aæ cool site
Aø cool site
Aå cool site

I want to have the database in UTF8.

I want to be able to sort correctly whatever language using a proper collation.....

Is it not possible?

(I have a bad feeling that I have to use another charset for the database, since there are no other collations to use than UNICODE and UCS_BASIC for the UTF8 charset.)


Doing the "same" in MySQL, ORACLE and it works like a charm.
Would really make this work in Firebird as well.

Did you also set the collation?

This should also solve your upper() problem

Check the PDF document inside this zip file from the Fourth International Firebird Conference it contains all the details

Hi Pabloj

Just downloaded the files, have not read it yet though.

Thanks for taking the time to give me these docs.

Hope it helps