Sorting by an alpha-numeric field (how to?)
Posted on 2005-03-08
Ok I have a field in a mySQL table called unit_num. It started out being a numeric field (which was all fine and good.) The problem is that the client has since changed thier mind and want to be able to add a letter to the end of the field (ie now they can have 1234 and 1234B). So I switched the field to a varchar field. That solved that problem. Now the situation has come up where I need to do searching on that field and sorting. I have a query which searches for any unit_num > x and <= y type thing. This obviously doesn't work correctly because mySQL doesn't know how a varchar field is greater than another (at least I assume it doesnt?) And I need to be able to sort by that column. I mean, I need to see 1233, 1234, 1234B, 1235, 1236, etc. How can this be done?? I am awarding 500pts, because it is critical that I get this sorted out ASAP!. Thanks in advance!