Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Voyager
    Ex Libris Knowledge Center

    How to identify MARC subfields missing punctuation mark at the end

    1. Last updated
    2. Save as PDF
    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    How might I find those records in our Voyager database that are missing the punctuation mark at the end of a specific subfield?

    Answer

    One option (there may be others) is to do the following:

    1. Use Global Data Change scan with the condition "MARC Field/Subfield Exists" to identify the records with the field/subfield you want to check.
    2. Use the record set created by Step 1 to import an external table into Prepackaged Access Reports
    3. Create a BLOB query using GetSubField to pull just the subfield you want to examine from the table created in Step 2 into a local table. Depending on the length of the field, it may need to be defined as "memo" or "long text" type.
    4. Create a second query to check the local table created in Step 3 with the following criteria: 
      Like "*[a-z]" Or Like "*[A-Z]" Or Like "*[0-9]"

    Additional Information

    Example BLOB query for Step 3 that pulls out the MARC 504 subfield a (local table of BIB_IDs imported from GDC is "504_TABLE" and local table being created is "504afield"):

    SELECT 504_TABLE.BIB_ID, (getsubfield(getfieldraw(getbibblob([BIB_ID]),"504",1),"a",1)) AS 504 INTO [504afield]
    FROM BIB_TEXT
    WHERE (((504_TABLE.BIB_ID)>'3000' And 504_TABLE.BIB_ID<'4000'));

     

    Example query for Step 4:

    SELECT [504afield].BIB_ID
    FROM [504afield]
    WHERE ((([504afield].[504]) Like "*[a-z]" Or ([504afield].[504]) Like "*[A-Z]" Or ([504afield.[504]) Like "*[0-9]"));

     

    Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes.  Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.

     

     

    • Article last edited: 20-Sept-2020