Sorting a varchar field alphabetically with correct numerical order help needed
I have a varchar 50 field that contains product names, which are typically numerical, alphabetical and punctuation thrown in. I would like to have them returned in some sort of order that is roughly alphabetical, but with the numbers in numerical order. The basic Order By clause does not do it correctly. An example is: SELECT setname FROM sets ORDER BY setname Sample values of setname are: 658 #1 JCAL 011 #2 NOV #11 NOV #12 NOV 985 ABC #123 NOV The results I get are: #1 JCAL #11 NOV #12 NOV #123 NOV #2 NOV <<<<<<<<< wrong 011 658 985 ABC The results I want are: #1 JCAL #2 NOV <<<<<<<<< should be here #11 NOV #12 NOV #123 NOV 011 658 985 ABC In the above, the #2 JCAL should be second, otherwise, list is correct. I could also live with the values beginning with # or any alpha character coming after the numerical ones, but the 1, 11, 2, order is the issue. Does anyone have any idea how to do this? I have been playing around with various suggested ways, including casting and converting, but so far have not been able to solve this. Any ideas would be greatly appreciated. Thanks, Rich
