Re: Sort order confusion

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
>
> The output I desire is a basic ASCII sort:
>
> somechars
> -----------
>     0
>     1
>     2
>     3
>    10
>   100
>   111
>   222
>   333
>  1011
>  1512
>
> (This sample set just has spaces and numeric digits but could have
> other characters - I want the output in ASCII sort order)

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.

If there were just digits that would be fairly easy, but I can't see any
way (short of post-processing the list in other software or writing
yourself a stored procedure) to do it in SQL.

    Stephen