Check out the function to_number()
In particular here's an example...
If a field named section is text containing numbers:
ORDER BY to_number(t.section, text(99999999))
If the field can also contain non-numerals such as 3a, 3b, and you want
3a to show first then do this:
ORDER BY to_number(t.section, text(99999999)), t.section
And if the field section can actually START with an alpha, then to
prevent to_number from failing do this:
to_number(textcat('0', t.section), text(99999999)), t.section
Terry
Bryce W Nesbitt wrote:
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
--
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
