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