How to order query by number first and then by string in the same field

Question

I have a db in PostgreSQL 9.0, which has a table with a string field to store client codes. These codes are alphanumeric and can start with a letter or number, for example 1, 2, A0001-4, A0001-2, 10

I want to order numeric first and then order by string, like

1, 2, 10, A0001-2, A0001-4

I do this with to_number(fields, '99999999'), for example:

SELECT * FROM empleados ORDER BY to_number(legajo, '99999999'), legajo

But when the code is like 've', with no number, the query fails.

What can I do?

Answer 1

WITH empleados(legajo) AS (
   VALUES
     ('A0001-4'::text)
    ,('123.345-56')
    ,('ve')
    ,('123')
    ,('123 ve')
   ) 
SELECT *
FROM   empleados
ORDER  BY CASE WHEN legajo ~ '\D' THEN 1000000000::int
                                  ELSE to_number(legajo, '999999999')::int END
      ,legajo;

~ is the regular expression operaor.
\D is the classs shorthand for non-digits.

Rows with non-digit characters in legajo (legajo ~ '\D') come later.

-> SQLfiddle demo

Never use SIMILAR TO, it's an utterly pointless operator.