7

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,

Community
  • 1
  • 1
Angel Palazon
  • 369
  • 1
  • 5
  • 16

3 Answers3

7

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).

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
mp_gt
  • 86
  • 2
  • 1
    Note that this creates a dependency between the size of the column and the ordering, which will cause no end of head-scratching should the column sized be changed. – Dave Jarvis Dec 29 '12 at 00:08
5

You can use regular expression to do this kind of thing:

select THECOL from ...
order by
  case
    when substring(THECOL from '^\d+$') is null then 9999
    else cast(THECOL as integer)
  end,
  THECOL

First you use regular expression to detect whether the content of the column is a number or not. In this case I use '^\d+$' but you can modify it to suit the situation.

If the regexp doesn't match, return a big number so this row will fall to the bottom of the order.

If the regexp matches, convert the string to number and then sort on that.

After this, sort regularly with the column.

Endy Tjahjono
  • 24,120
  • 23
  • 83
  • 123
1

I'm not aware of any database having a "natural sort", like some know to exist in PHP. All I've found is various functions:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502