Figured I'd chime in with something I spent entirely way too much time on
(probably). A JavaScript class to normalize and sort LC call numbers

https://github.com/rayvoelker/js-loc-callnumbers

Not sure if that helps you in your particular situation, but it might give
you a place to start along with the other suggestions given!

Good luck!

--Ray

On Oct 25, 2017 5:16 PM, "Craig Dietrich" <[log in to unmask]> wrote:

> 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
> >>
>