Sorting by an alpha-numeric field (how to?)

Posted on 2005-03-08
Medium Priority
Last Modified: 2012-06-27
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!
Question by:electricd7
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Accepted Solution

lokus earned 2000 total points
ID: 13492337
You can still order by the number but not the alphabet behind.

ORDER BY unit_num+0

More need to be done if you need to order by the number then the alphabet after the number.

Maybe something like
ORDER BY unit_num+0, substring(unit_num,length(unit_num+0)+1)
Not sure if this works or not.


Author Comment

ID: 13495986
Ok the first one seemed to work for my purposes.  So you get the points, but I am still not getting what I need.  I think you also helped me on my previous question as well, so maybe you can help me pull it all together.  If I use the following query:

SELECT * FROM bbank  ORDER BY unit_num+0

I get the records in the correct order.  Now, what I need to do is pass a record, lets say 4800420B, to another page.  Ok so far so good.  Now on that page I want to show 41 records, the 20 records which come before that record ordered by unit_num, and then the 20 records which come after that record.  I am just not sure how to get that set, since I can't really do a > <= because unit_num is a string.  I mean the top query works fine, but I just don't understand how to get the second page to give me the records I need.  Thanks in advance!

Expert Comment

ID: 13500187
Sure you can use the > <= comparison, do the same thing to cast the string into numbers, unit_num+0 > 4800420
Of course, cannot have the alphabets here too since it will not be a vaild number.

You can add +0 to a string column to cast it to numbers.

Author Comment

ID: 13500226
ahh..ok that all makes sense then.  Again thanks for all your help, and enjoy the points! :)

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question