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@inLibro.com <mailto:caroline.cyr-la-rose@inLibro.com> INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com <http://www.inLibro.com>