This SQL query will search for find call numbers with carriage returns :
SELECT
ie.instance_hrid,
he.holdings_hrid,
he.holdings_id,
he.call_number
FROM folio_reporting.instance_ext ie
LEFT JOIN folio_reporting.holdings_ext he ON he.instance_id= ie.instance_id
WHERE he.call_number ~ '\n'
;
Building on the query above, this statement will find carriage returns and new line feed characters in call numbers and remove them, using the Replace function:
Note: chr(10) is a line feed; chr(13) is a carriage return:
with recs as
(SELECT
...
select
FROM folio_reporting.instance_ext ie
LEFT JOIN folio_reporting.holdings_ext he ON he.instance_id= ie.instance_id
...
he.call_number
...
)
SELECT
recs.call_number,
replace (replace (recshe.call_number, chr(13), ''),chr(10),'') as fixed_call_number
from folio_reporting.holdings_ext as he
where he.call_number ~ '\n'FROM recs
;
