- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
How might I find those records in our Voyager database that are missing the punctuation mark at the end of a specific subfield?
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]"
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]
WHERE (((504_TABLE.BIB_ID)>'3000' And 504_TABLE.BIB_ID<'4000'));
Example query for Step 4:
WHERE ((([504afield].) Like "*[a-z]" Or ([504afield].) Like "*[A-Z]" Or ([504afield.) 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