In Analytics, "Network Number" contains all OCLC numbers, separated by a semicolon; the OCLC number SQL query looks for one OCLC number hierarchically.

• Convert to UPPER to prevent case typos
• Look for items in this order: OCOLC, OCM, OCN, ON.
• In the case when the value contains OCOLC, locate OCOLC and keep everything from the left of OCOLC to the first semicolon.  Then replace the semicolon with nothing.
• Repeat for OCM, OCN, and ON.
• If none of these values are found, replace value with “No OCLC Number Available.”

CASE WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCOLC%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('(OCOLC',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('(OCOLC',UPPER("Bibliographic Details"."Network Number"))))),';','')  WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCM%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('OCM',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('OCM',UPPER("Bibliographic Details"."Network Number"))))),';','')  WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCN%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('OCN',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('OCN',UPPER("Bibliographic Details"."Network Number"))))),';','') WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%ON%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('ON',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('ON',UPPER("Bibliographic Details"."Network Number"))))),';','') ELSE 'No OCLC Number Available' END