If your string *always* begins with a numeral, this will work:
ORDER BY to_number(text_field, text(99999999)), text_field
If it doesn't always begin with a numeral, you have to ensure that it does, so a textcat of zero
ensure it does...
ORDER BY to_number(textcat('0', text_field), text(99999999)), text_field
That works provided your number is never negative, (a reasonable assumption I think).
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Robert
> Fitzpatrick
> Sent: Tuesday, August 10, 2004 8:55 PM
> To: PostgreSQL
> Subject: [GENERAL] Sorting varchar w/single digits
>
>
> I have varchar column with both numbers and letters, like 1
> thru 10 and
> 5A thru 5G, they are unit numbers for apartments. If I have 1
> thru 100,
> since it is a varchar field, it sorts like 1,10,11... instead of
> 1,2,3...
>
> Is there any way to handle this without having to make a sort order
> column?
>
> --
> Robert
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>