This SQL will search for 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 characters and remove them using the Replace function:
Note: chr(10) is a line feed; chr(13) is a carriage return
with recs as
(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'
)
SELECT
recs.call_number,
replace(replace (recs.call_number, chr(13), ''),chr(10),'') as fixed_call_number
FROM recs
;