On Tue, Aug 10, 2004 at 08:54:45PM -0400, Robert Fitzpatrick wrote:
> 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...
>
>
If your string *always* begins with a numeral, this will work:
ORDER BY to_number(text_field, text()), 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())
