Re: sorting invoice numbers by numerical order

[Chris Travers <..hidden..>]

On Tue, Feb 16, 2010 at 2:23 PM, Luke <..hidden..> wrote:

> Does psql have anything along the lines of the MySQL convert builtin?
>
> If so, running the following probably works:
>
> select ... order by convert(invnumber as unsigned) asc;
>
> In MySQL, at least, that properly sorts:
>
> 2
> 1
> 10
> 9C
>
> into:
>
> 1
> 2
> 9C
> 10

The way to do this in Pgsql is:

ORDER BY substring(invnumber from '[0-9]*')::int

>
> Alternatively, Would it be possible to group by length?

group by length(invnumber), invnumber?

the issue is more a matter of the Perl code than the SQL, actually.

Best Wishes,
Chris Travers