If you're using PHP you could use natsort() on the result,

http://php.net/manual/en/function.natsort.php

Alternatively, you could try ordering by the length of the VARCHAR field first,

ORDER BY LENGTH(field), field 

Sent from mobile

> On Oct 25, 2017, at 2:11 PM, Jodie Gambill <[log in to unmask]> wrote:
> 
> Hi Will -
> I had a similar task a few years ago on a small project, though we only
> used the classMark and classNum (from your example) parts of the call
> number for what we needed. I implemented it as you outlined above, with two
> separate fields to enable proper sorting -- classMark as varchar and
> classNum as float. I look forward to hearing if anyone else has done it
> more efficiently!
> -Jodie
> 
>> On Wed, Oct 25, 2017 at 3:55 PM, Will Martin <[log in to unmask]> wrote:
>> 
>> We have a small web app with a MySQL backend, containing lists of books
>> that have been reported lost and tracking our efforts at locating them.
>> Access Services requested that the list of currently missing books be
>> sorted according to LC call number.  So we did, but the results are ordered
>> in that uniquely digital "1000 comes before 2" way.  For example, human
>> logic would sort these seven call numbers like so:
>> 
>>        HM132 .A343
>>        HM251 .M2 1960
>>        HM278 .S37 1990
>>        HM281 .C6713 1985
>>        HM281 .H6 1958
>>        HM1126 .F56 2011
>>        HM1261 .K64 1978
>> 
>> But MySQL sorts them like so:
>> 
>>        HM1126 .F56 2011
>>        HM1261 .K64 1978
>>        HM132 .A343
>>        HM251 .M2 1960
>>        HM278 .S37 1990
>>        HM281 .C6713 1985
>>        HM281 .H6 1958
>> 
>> Currently, these are stored in a VARCHAR column in the MySQL table.  The
>> research that I've done suggests that there is no way just sort that column
>> using ORDER BY, and that we'll have to split out the call number into
>> separate columns for its component parts:
>> 
>>        HM = classMark
>>        251 = classNum
>>        .M2 = cutter
>>        1960 = pubYear
>> 
>> ... and then do something like "ORDER BY classMark, classNum, cutter,
>> pubYear".
>> 
>> This database is unlikely to grow beyond a few hundred active records at
>> most, so I'm not too concerned about the computational overhead of doing
>> all that sorting.  But it's going to be a fair bit of work to modify the
>> table definition and then rejigger all the associated SQL queries in the
>> app.
>> 
>> Does anyone have a better solution for sorting LC call numbers in MySQL
>> results?
>> 
>> Thanks.
>> 
>> Will Martin
>> 
>> Head of Digital Initiatives, Systems & Services
>> Chester Fritz Library
>> University of North Dakota
>>