Sorting varchar w/single digits

[Robert Fitzpatrick]

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 ma*******@postgresql.org)

[terry]

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
te***@greatgulfhomes.com
Fax: [phone]

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@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
ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Michael Fuhr]

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

Is there any way to handle this without having to make a sort order
column?

Try something like this:

ORDER BY SUBSTRING(unitnum FROM '[0-9]+')::INTEGER, unitnum

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly