[Koha] Batch edit to control fields?

[Caroline Cyr-La-Rose]

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

SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),33,1)

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

SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),34,1)

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

SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),16,3)

Does that make sense?

Caroline

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 
<http://www.inLibro.com>