How to identify MARC subfields missing punctuation mark at the end
- 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:
- Use Global Data Change scan with the condition "MARC Field/Subfield Exists" to identify the records with the field/subfield you want to check.
- Use the record set created by Step 1 to import an external table into Prepackaged Access Reports
- 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.
- 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