Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

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

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!
0
electricd7
Asked:
electricd7
  • 2
  • 2
1 Solution
 
lokusCommented:
You can still order by the number but not the alphabet behind.

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



0
 
electricd7Author Commented:
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!
0
 
lokusCommented:
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.
0
 
electricd7Author Commented:
ahh..ok that all makes sense then.  Again thanks for all your help, and enjoy the points! :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now