From: Kay-Michael Goertz
Date: November 5 2000 7:07pm
Subject: Re: Unicode
List-Archive: http://lists.mysql.com/internals/102
Message-Id: <3A05AFD6.A7B02430@talknet.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

There is no universal sorting algorithm. But there are sorting orders
depending to a country. I want to give the database in a sql query the
order undepending in which order data are stored.

There is not such a thing in sql standard (as I know). So we have to
enhanced the syntax of the order clause. If there would be the possibility
to give an order function, than the problem could be solved. This can work
to "normal" varchar data to, but there are less problems with different
ordering.

With unicode everbody could use his own collation algorithm, the data can
be sorted in a german or an english way. And such a function is easy to
implement.
All you need is an array, in which you give the position for every
character, you need. Other characters can be aspected as behind this and
ordered in binary way.

We can also use an array to implement a functions to lowercase(uppercase or
to get the type of an character).

If we take UCS-2, we need 2 bytes for a character. With 5 columns we need
65KB * 2 byte * 5, less than 1MB.
In column one we will store the lowercase variant of the character, if
there is one. If not we store 0.
In column two we will store the uppercase variant of the character, if
there is one. If not we store 0.
...

The function ToUppercase would now look like

if(char<>0)
{
    if(char <> array[char][1])
       if(array[char][1]<>0)
            return array[char][1];
 }
return char;

The function IsUppercase would look like

if(char<>0)
{
    if(char == array[char][1])
        return true;
 }
return false;

So the implementation will be fast, but we need memory for this.