[Koha] Batch edit to control fields?

Caroline Cyr-La-Rose caroline.cyr-la-rose at inlibro.com
Wed Aug 25 05:19:26 NZST 2021

There is a particularity with substring and control fields in SQL.

The difference has to do with the starting character of the substring, 
i.e. the 33 in the following sequence


MARC starts counting at 1, whereas SQL starts counting at 0. Or it might be the reverse, I don't remember exactly. Anyway, the thing is that you can't use the MARC character position (in this case 33 because you're looking for 008/33), directly in the substring statement. You have to add 1.

So to look for 008/33 you have to use


to look for 008/15-17, like in the example in the link I sent, you have to use


Does that make sense?


On 2021-08-24 12 h 13, Cab Vinton wrote:
> Thanks so much, Caroline! Good excuse to brush up on my French.
> Have used MarcEdit before, but not for this, so your instructions will
> be vachement utiles :-)
> Btw, we've been using the report below to identify which records have
> issues w/ the 008/33. However, the ExtractValue statement is pulling
> spaces rather than the actual values.
> Would be grateful for any suggestions on why the SUBSTRING &
> ExtractValue statements don't appear to be working well together.
> All best,
> Cab Vinton, Director
> Plaistow Public Library
> SELECT SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),33,1)
> AS LitForm, i.itemcallnumber, i.itype, i.ccode, CONCAT('<a
> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber,
> '\">', biblionumber, '</a>' ) AS bibnumber
> FROM biblio_metadata
> LEFT JOIN items i USING (biblionumber)
> ORDER BY i.biblionumber DESC, i.itemcallnumber
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
Caroline.Cyr-La-Rose at inLibro.com <mailto:caroline.cyr-la-rose at inLibro.com>

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 

More information about the Koha mailing list