Re: Sort order confusion

On Thursday 05 February 2004 4:21 pm, Stephen Robert Norris wrote:
> On Fri, 2004-02-06 at 11:12, Steve Crawford wrote:
> > I am suffering some sort order confusion. Given a database,
> > "foo", with a single character(4) column of data left padded with
> > spaces I get:
> >
> > select * from foo order by somechars;
> >
> > somechars
> > -----------
> >     0
> >     1
> >    10
> >   100
> >  1011
> >   111
> >  1512
> >     2
> >   222
> >     3
> >   333
<snip>


> Your original sort is a basic lexigraphic ("alphabetical" by ASCII
> character set number) sort.
>
> What you appear to want is a numeric sort, where the numbers come
> out in the order of numbers, rather than in their ASCII character
> set order.

Not exactly. I _DO_ want it in ASCII character set order which
includes spaces (0x20) sorting ahead of digits (0x30 - 0x39). This is
not what is happening. The first sort is some SQL sort order that
seems to ignore certain characters. Note the different sort order if
I pad with 'x' instead of '<space>':

somechars
-----------
 1011
 1512
 x100
 x111
 x222
 x333
 x444
 x555
 x666
 x777
 x888
 xx10
 xx44
 xx55
 xxx0
 xxx1
 xxx2
 xxx3
 xxx4
 xxx5
 xxx6
 xxx7
 xxx8
 xxx9
 xxxx

Naturally if I were dealing with fields guaranteed to have something
that would convert to an int I could just order by, say,
int4(somechars) but that is not the case.

I even tried the to_ascii function but apparently that's the wrong
approach:
ERROR:  encoding conversion from SQL_ASCII to ASCII not supported

Cheers,
Steve