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
>