How do I sort a VARCHAR column in PostgreSQL that contains words and numbers?

Question

I need to order a select query using a varchar column, using numerical and text order. The query will be done in a java program, using jdbc over postgresql.

If I use ORDER BY in the select clause I obtain:

1
11
2
abc

However, I need to obtain:

1
2
11
abc

The problem is that the column can also contain text.

This question is similar (but targeted for SQL Server):

How do I sort a VARCHAR column in SQL server that contains words and numbers?

However, the solution proposed did not work with PostgreSQL.

Thanks in advance, regards,

Answer 1

I had the same problem and the following code solves it:

SELECT ...
  FROM table
  order by  
    CASE WHEN column < 'A' 
        THEN lpad(column, size, '0')
    ELSE column 
        END;

The size var is the length of the varchar column, e.g 255 for varying(255).