“Fixing” ascii sorting in SQL Reports

A quick-n-dirty SQL tuturial

The Problem

Recently there was a request from IDSK – they wanted the accession register SQL report for their Rabindranath Tagore Grant collection books to be listed in a natural sort. Documents of this collection carry the prefix “RT” which is fine and dandy. The “problem” started after that. In their earlier legacy system (SOUL v1) when these books had been accessioned, they did not use an fixed uniform length accession no for this collection. For example, instead of RT00001, it was entered RT1, RT2 and so on. Further, their problem was compounded by another glitch. In several of these accession nos, there were spaces inserted between “RT” and the actual numerical part. When they migrated to Koha with L2C2 Technologies, these problems were carried over to the new system as data curation and correction was not part of the deliverables. Now, three years later, when reports were being asked for, these issues had come back to haunt them.

The “Solution”

The RT collection has over 4000+ books and a manual fix was not feasible at this time. So using the ORDER BY keyword in the report’s SQL like this: ORDER BY LPAD(REPLACE(REPLACE(TRIM(items.barcode)," ",""),"RT",""), 10, ' ') ASC we at least managed to hide the problem and also got natural sorting to work. The first REPLACE() took out any space in the accession no, and the second REPLACE() stripped off the “RT” prefix, leaving us with only the number. Finally using LPAD() we zero prefixed the number so that each one was exactly 10 characters long. And we had our naturally sorted list of accession numbers in the report as we can see below.

1 thought on ““Fixing” ascii sorting in SQL Reports”

  1. It seems that also the author field needs a serious massage 🙂
    – there are spaces between names and surnames,
    – there are no spaces between names and surnames,
    – names come before surnames, etc.
    Say, for ex., if you want to give an alphabetical order to the author field
    you will get all the authors called Jennifer or J.G.A. together under the letter ‘J’ and
    not under their respective surnames (in this case, under ‘Munroe’ and ‘Pocock’)…
    what to speak about the presence of unnecessary full stops…
    Unless I am wrong, of course 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *