[SQL] casting character varying to integer - order by numeric sort

[Bryce W Nesbitt]

How can I force a character field to sort as a numeric field?
I've got something like this:
Postgres=> SELECT username,last_name
                 FROM eg_member ORDER BY username;
----------+-----------
0120     | Foley
1        | Sullivan
10       | Guest
11       | User
(5 rows)

(I can't change the field type).  I tried:

 SELECT username,last_name
 FROM eg_member ORDER BY username::integer;

But postgres 7 rejects this with "ERROR:  cannot cast type character 
varying to integer".  Is there a way to force numeric sort order?  I 
tried a variety of functions, such as to_char() and convert() without 
any luck.  Thanks for your insight!


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster