If the issue is that you don't want to actually have to separate out
additional columns and/or use some sort of temp table to hold them, you
can work around that by pulling out some substrings in the order by. I
work primarily in SQL but i'm pretty sure patindex ("pattern index") works
in MySQL as well. You can use it to reference various points in the string
like the first alpha character, first numeric character, etc. Using that
with substring lets you get just the pieces you need and if you throw a
convert in there you can then treat the numeric parts as numbers.
I think you could do something like this:
ORDER BY
substring(Column1,patindex('%[a-z]%',Column1),patindex('%[1-9]%',column1)-1),
--this gets just the first alpha part of the string
convert(int,substring(column1,patindex('%[1-9]%',column1),charindex('
',column1)- patindex('%[1-9]%',column1)) ), --this gets from the first
number to the first space, and converts it to an int so it'll sort right
column1 --all things being equal on the first two items, i think then you
can just sort by the column itself and you should be good?
I tried this on your values and they're sorting in the order you're looking
for
On Wed, Oct 25, 2017 at 5:38 PM, Will Martin <[log in to unmask]> wrote:
> 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
>>> >>
>>>
>>>
