Re: sort output per alpha-numeric?

On Wed, Dec 1, 2021 at 3:38 PM Sbob <sbob(at)quadratum-braccas(dot)com> wrote:

> I get a list like this:
>
> print_size
> ------------
> 11x14
> 16x20
> 20x24
> 24x30
> 32x40
> 40x50
> 8x10
> (7 rows)
>
> I want the displayed print_size to be ordered by size (8x10, then 11x14,
> etc)
>
> Is there an easy way to do this?
>
Sometimes over-sharing is just as bad as under-sharing...consider creating
minimalistic examples, usually with the help of a CTE to provide data
directly within the query and avoiding the need for tables altogether.

You can sort by an expression. For the data as shown the following should
work:

ORDER BY CASE WHEN print_size ~ '^\dx' THEN '0' || print_size ELSE
print_size END

In short, the least invasive solution is to just prepend a zero to a single
digit size.

If this isn't sufficient (e.g., if the second dimension causes the issue)
you may need to break the two-part string into two separate fields, convert
them to integers, and then sort on the pair.

You could also create a custom type and define a custom comparison
function...

David J.