Sorting Numbers in a VarChar Field

[ggggus]

I have a table that has a small alpha-numeric varchar(5) field in it. Field is used as a key for some diagrams. Some of the diagrams are labeled with numbers only (ex. 1,2,3,4...) and others are labled with alpha numerics (ex. A1,A2,B1,B2...).

I'm running into a problem sorting the field properly. When I sort a query that contains only numbers I get results like the following:

1
11
12
13
2
3

If the field is specified as an intiger is sorts properly.

1
2
3
11
12
13

How do I get this field to sort proplery for both numbers and alpha-numerics? Current language for the field is latin1, will changing this to another language type correct this? and if so what language?

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[guelphdad]

select 
yourfield 
from yourtable 
order by 
yourfield+0

 

[TonyGroves]

To sort both the numeric and alphanumeric values you would need:
[tt]
ORDER BY yourfield+0,yourfield
[/tt]

 

[ggggus]

Thanks guys, this gets me about half the way there, but it still doesn't work perfectly. The field is now a varchar(5) and orders numbers perfectly now...but still I get the following alpha-numeric numbering

A1
A11
A12
A2
A3
A4

Is there a way to fix this as well?

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[TonyGroves]

You'll have to tell us more about what ordering you want. Are you saying you want the leading alphabetic part ignored for sorting purposes and the numeric and alphanumeric values mixed together, or the alphanumeric values before or after the numerics?

 

[ggggus]

I'm dealing with diagrams from various manufacturers for some equipment, and they have the manufacturers will number the diagrams, and then on a seperate list convert the "bubble number" into a part list. So the number on the diagram is a unique key for that specific diagrams.

Some manufacturers will number their diagrams with numbers and a ocasional letter postfix (1,1a,1b,2,3,4...) others will use letters only (A,B,C,D) still others will use leading letter (sometimes two) (A1,A2,A3..A10,A11,B1,B2) or (AA1,AA2,AB1,AB2).

These methods are never mixed, it's usually the same for every diagram that a particular manufacturer creates for it's equipment, but different manufacturers have their own "number" convention that they use.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[TonyGroves]

OK, but exactly what ordering system is required?

 

[ggggus]

The orders illustrated in my last message would be approprate for each of those sequencings. "Natural Sorting" is what I'm looking for.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[TonyGroves]

Thare are so many different systems in use here that I don't think any single ORDER BY clause would be able to cope (without enormous complication). If you can't arrange to have the codes stored in a uniform format (e.g. a prefix of 2 letters/spaces, then a number of 3 digits, then an optional suffix letter), then you would need to use program code to put them into a uniform format before sorting them.