Re: Order By and Comparisson

On Mon, 07 Apr 2014 11:04:23 +0100
"howardnews(at)selestial(dot)com" <howardnews(at)selestial(dot)com> wrote:

> Hi,
>
> just as I thought I had postgres mastered :) the ordering of strings is
> causing me some confusion.
>
> Can someone explain how the database orders strings in the ORDER BY command.
>
> My example:
>
> My database is encoding is UTF-8, and default language is english,
>
> If I have a text column in a table with the following rows:
>
> 'a'
> 'A'
> '~'
>
> Then in UTF-8, I would expect the order to give me
>
> 'A'
> 'a'
> '~'
>
> But instead I get:
>
> '~'
> 'a'
> 'A'
>
> Is there anywhere in the documentation I can get a more detailed
> explanation of this?

Expect for the unexpected. SQL alphabetical sorting can get pretty complicated,
as stated in this note from Oracle 10g Release 2 docs[1]:

"In the ASCII standard, all uppercase letters appear before any lowercase letters.
In the EBCDIC standard, the opposite is true: all lowercase letters appear before
any uppercase letters".

[1] docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm

--
Alberto Cabello Sánchez
<alberto(at)unex(dot)es>