[CODE4LIB] Sorting LC call numbers in MySQL

[Will Martin]

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