 |
 |
I'm late to the party here, but one more potential option for you: https://github.com/unt-libraries/pycallnumber Another python package for dealing with call numbers, but in this case it may be helpful if you find your call number data is a bit messy. I wrote it after having to deal with large sets of call numbers from our catalog that either weren't actually what they said they were (SuDoc numbers in LC fields, for instance, where I had to handle them correctly regardless) or had unpredictable localizations that threw off attempts at sorting (particularly things like dates and other free-text or semi-structured edition/volume/copy information). Good luck! Jason -----Original Message----- From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of Will Martin Sent: Wednesday, October 25, 2017 4:38 PM To: [log in to unmask] Subject: [EXT] Re: [CODE4LIB] Sorting LC call numbers in MySQL Wow, options popping out of the woodwork left and right! We'll probably try out several of these and see which suits best. Thanks a lot, everyone! Will On 2017-10-25 16:31, Ray Voelker wrote: > 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 >> >> >>